PIVOT in Oracle 11G to Select Rows As Columns

A frequent requirement in SQL is to "pivot" a result set — that is, display rows as columns. Before 11G, the developer had to do the equivalent of jumping through hoops to pivot the data. In 11G, with the introduction of the new PIVOT syntax, the task of transposing rows to columns has become a lot more intuitive.

This post shows the use of PIVOT with an example and sample scripts.

Let’s take a table CONTACT that stores contact details of persons — landline numbers, mobile numbers, website URLs. A person can have multiple contacts of each type, and each contact type has a priority associated with it.

The requirement is to display the primary contact (priority = 1) of each type — Landline, Mobile, Website — as columns.

This is the entire data in the table CONTACT:

SQL> select person_key
  2         , contact_type_code
  3         , contact_detail
  4         , priority
  5  from contact
  6  order by person_key
  7         , contact_type_code
  8         , priority;

PERSON_KEY CONTACT_TY CONTACT_DETAIL     PRIORITY
---------- ---------- ------------------ --------
        12 LANDLINE   10234126                  1
        12 LANDLINE   10234124                  2
        12 MOBILE     81342122                  1
        12 WEBSITE    www.12mysite1.com         1
        12 WEBSITE    www.12mysite2.com         2
        12 WEBSITE    www.12mysite3.com         3
        14 MOBILE     6467433                   1
        14 MOBILE     5557433                   2
        14 WEBSITE    www.14site1.com           3

This is the data we are interested in:

SQL> select person_key
  2         , contact_type_code
  3         , contact_detail
  4         , priority
  5  from contact
  6  where priority = 1
  7  order by person_key
  8         , contact_type_code
  9         , priority;

PERSON_KEY CONTACT_TY CONTACT_DETAIL     PRIORITY
---------- ---------- ------------------ --------
        12 LANDLINE   10234126                  1
        12 MOBILE     81342122                  1
        12 WEBSITE    www.12mysite1.com         1
        14 MOBILE     6467433                   1

And this, the result set we want:

PERSON_KEY LANDLINE   MOBILE     WEBSITE
---------- ---------- ---------- -----------------
        14            6467433
        12 10234126   81342122   www.12mysite1.com

Pre 11G: Using DECODE

Before 11G, the popular solution to the pivot problem was with a combination of max and decode.

The SQL:

-- Pre 11G solution: rows to columns
select person_key
    , max(decode(contact_type_code
	, 'LANDLINE', contact_detail)) landline
    , max(decode(contact_type_code
	, 'MOBILE', contact_detail)) mobile
    , max(decode(contact_type_code
	, 'WEBSITE', contact_detail)) website
from contact
where priority = 1
group by person_key;

When run:

SQL> -- Pre 11G solution: rows to columns
SQL> select person_key
  2      , max(decode(contact_type_code
  3   , 'LANDLINE', contact_detail)) landline
  4      , max(decode(contact_type_code
  5   , 'MOBILE', contact_detail)) mobile
  6      , max(decode(contact_type_code
  7   , 'WEBSITE', contact_detail)) website
  8  from contact
  9  where priority = 1
 10  group by person_key;

PERSON_KEY LANDLINE   MOBILE     WEBSITE
---------- ---------- ---------- -----------------
        14            6467433
        12 10234126   81342122   www.12mysite1.com

In 11G: Using PIVOT

With the PIVOT syntax, the same query can be written in a more compact, readable form.

The SQL:

-- 11G solution (PIVOT): rows to columns
select *
from   (select person_key
             , contact_type_code
             , contact_detail
        from   contact
        where priority = 1)
pivot  (max(contact_detail) 
       for (contact_type_code) in
       ('LANDLINE'
      , 'MOBILE'
      , 'WEBSITE'));

When run:

SQL> -- 11G solution (PIVOT): rows to columns
SQL> select *
  2  from   (select person_key
  3               , contact_type_code
  4               , contact_detail
  5          from   contact
  6          where priority = 1)
  7  pivot  (max(contact_detail) 
  8         for (contact_type_code) in
  9         ('LANDLINE'
 10        , 'MOBILE'
 11        , 'WEBSITE'));

PERSON_KEY LANDLINE   MOBILE     WEBSITE
---------- ---------- ---------- -----------------
        14            6467433
        12 10234126   81342122   www.12mysite1.com

Notes and References

PIVOT queries look a lot more intuitive than their pre-11G alternatives, but the restriction of having to hard-code the pivot values still remains. If a subquery is used instead of a fixed list in the pivot IN clause, Oracle raises an error.

SQL> select *
  2  from   (select person_key
  3               , contact_type_code
  4               , contact_detail
  5          from   contact
  6          where priority = 1)
  7  pivot  (max(contact_detail) 
  8         for (contact_type_code) in
  9  -- Subquery instead of list of values
 10         (select distinct 
 11          contact_type_code
 12          from contact));
       (select distinct
        *
ERROR at line 10:
ORA-00936: missing expression

This restriction is relaxed in case the result set is generated in XML format with the "XML" keyword — this article gives details about its use. You will also find there more examples of pivoting with column aliases, pivoting on multiple columns, and performance analysis of pivot queries.

For further reading: with PIVOT around, can UNPIVOT be far behind?