Oracle's PIVOT Clause -- A Simple Example

Oracle's PIVOT clause allows us to take information that is 'stored in rows' and 'pivot-it-out' so that it is 'stored in columns'.

We need a column (or set of columns) that contains a finite number of identifying values -- these values will determine what new columns are created. In the following example the "qtr" column contains the yearly quarter. Instead of showing our data 'vertically' as shown below, we wish to take the information in the "qtr" column and spread our data out 'horizontally' so we have a column (or set of columns) for each quarter.




Here is our sample data and a simple PIVOT query that accomplish this pivot by quarter.

   WITH  
     w_data  as
       ( select   11 acct,  1 qtr,  23.29 m1   from dual union all
         select   11     ,  2    ,  81.72      from dual union all
         select   11     ,  3    ,  16.72      from dual union all
         select   11     ,  4    ,  20.00      from dual union all
         select   11     ,  4    ,  20.00      from dual union all
         select   11     ,  4    ,  20.00      from dual union all
         select   35     ,  2    ,  11.53      from dual union all
         select   35     ,  3    ,  51.27      from dual union all
         select   35     ,  4    ,  76.14      from dual union all
         select   44     ,  1    ,  53.63      from dual union all
         select   44     ,  2    ,  38.58      from dual union all
         select   44     ,  3    ,  29.76      from dual union all
         select   44     ,  4    ,  47.12      from dual 
       )
   select  *
     from  w_data
    PIVOT  ( sum(m1)   as sm
           , count(*)  as ct
           FOR  qtr  IN 
              ( 1 as q1
              , 2 as q2
              , 3 as q3
              , 4 as q4 
              )
           ) 
    order  by acct ;

And here is the result from running the above query:


Explanation:

First off -- the PIVOT clause is an aggregate operation. It's like having a "Group By" clause in your query without having one spelled out. Any column that is not used inside the "PIVOT" clause is treated as a "Group By" attribute. In this example the "acct" column is the only one not used in the pivot clause and thus the query will "Group By" the "acct" values -- We see that we get one final row for each "acct" value.

The first part of the PIVOT clause contains the definitions of one or more measures (comma separated). In this case we define the "sum(m1)" as a measure called "sm" and a count of the rows in each group as a measure called "ct". If you only define one measure there is no need to specify an alias name -- two or more measures... alias names must be defined.

After the "FOR" key-word, the column (or set of columns) that contains the identifiers that will determine our newly generated 'pivot' columns must be named. In this case the "qtr" column will contain the pivot-column identifiers.

After the "IN" key-word a list of all the identifiers that are of interest is included in parenthesis with their accompanying alias names. Depending on the data and number of identifying columns listed in the "FOR" section, alias names may or may not be required. When the query is executed a "pivot-group" is created for each distinct value in this list.

A new PIVOT column is generated for each defined measure for each "pivot-group". In this case we have 4 "pivot-groups" and 2 measures -- So 8 new columns are generated (4 x 2 = 8). The newly generated column names are created by joining the "pivot-group" aliases with the measure's aliases (joined with an underscore).

The "m1" measure is aggregated -- the values are added up if there is more than one value for each "acct" and "qtr". The "ct" columns show how many values were aggregated for each "acct" and "qtr". In the select clause of the query, you may choose to display as many or as few of the newly generated pivot columns as you wish to display -- in any order.



Here's one final example showing that you can display the columns in any order, and that you can even use the columns as normal columns (i.e. using them as part of a calculation).

 
   /* Include the WITH clause sample data from above here. */ 
   select  acct
        ,  q1_sm , q2_sm , q3_sm , q4_sm
        ,  q1_ct + q2_ct + q3_ct + q4_ct  as ttl_ct
     from  w_data
    PIVOT  ( sum(m1)   as sm
           , count(*)  as ct
           FOR  qtr  in 
              ( 1 as q1
              , 2 as q2
              , 3 as q3
              , 4 as q4 
              )
           ) 
    order  by acct ;