Oracle's UNPIVOT Clause -- A Simple Example

To "Un-Pivot" data generally means to take the values from multiple columns and place them in a single column.

Here we have a table that contains measures for each quarter of the year for various accounts. We would like to 'un-pivot' the data so that all the measures that are currently contained in the four columns ( Q1, Q2, Q3, and Q4 ) will be located in one single column. In order to not lose any information we will also need a new column that will contain an indicator that tells us which column (which quarter, in this case) the specific measure came from.




Here's the "UNPIVOT" query (it also contains the sample data above) that will do the trick:

/* ===== code ===== */     
   

   WITH
     w_data  as
       ( select  11 acct, 23.29 q1, 81.72 q2, 16.72 q3, 60.00 q4  from dual union all
         select  35     , null    , 11.53   , 51.27   , 76.14     from dual union all
         select  44     , 53.63   , 38.58   , 29.76   , 47.12     from dual
       )
   select  acct,  qtr,  m1
     from  w_data
  UNPIVOT  include nulls
           (   m1  FOR  qtr  IN
             ( q1   as   1
             , q2   as   2
             , q3   as   3
             , q4   as   4
             )
           )
    order  by 1, 2 ;

Explanation: 

We add the "UNPIVOT" clause immediately after the "FROM" clause. If we want a place-holder for each account and quarter (even if the measure is NULL) then we can include the phrase "Include Nulls" as shown. Inside the main parenthesis of the UNPIVOT clause we first create two new columns. Prior to the keyword "FOR" we create the column that will contain the measures (m1). After the keyword "FOR" we create the column that will contain the identifying values that tell us which original column the measures came from (qtr). Then, following the keyword "IN" we list the columns and idetifying values as shown above. The following is an English translation of what is being said in this "IN" clause:

          Values in col. Q1 will be placed in the new col. M1 and identified by a 1 in col. QTR.
          Values in col. Q2 will be placed in the new col. M1 and identified by a 2 in col. QTR.
          Values in col. Q3 will be placed in the new col. M1 and identified by a 3 in col. QTR.
          Values in col. Q4 will be placed in the new col. M1 and identified by a 4 in col. QTR.

And, of course, the value in the ACCT column will stay the same for each measure brought into the result-set. The result of this query is shown below:





The previous example shows how a simple un-pivot statement works. In more complex un-pivot queries, you can have as many columns as you wish in order to show the desired identifying values and you can pivot the multiple columns in your source data into multiple measure columns in the result set. (More to come.)