Oracle PIVOT and UNPIVOT to Invert Data

Note: Prior to going through this example, you may wish to make sure you understand how to use Oracle's basic PIVOT and UNPIVOT clauses.

Let's suppose we have some data that we would like to "Invert" -- the rows become the columns and the columns become the rows. Here's a simple example that has account numbers for rows and quarters as columns. Note that the "Inversion" is shown with the quarters as rows and the account numbers as the columns, and the measures have been retained to correspond with the same quarters and accounts:





So, here is our sample data and the first part of the solution. We'll do an UNPIVOT to bring back all the measures into a single column:

/* ===== code ===== */     
   
   WITH
     w_data  as
       ( select  11 acct, 23.29 q_1, 81.72 q_2, 16.72 q_3, 60.00 q_4  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  *
     from  w_data
  UNPIVOT  include nulls
           (   m1   FOR   qtr  IN
             ( q_1   as   1
             , q_2   as   2
             , q_3   as   3
             , q_4   as   4
             )
           )
    order  by 1 ;

Notice that we created the QTR column and assigned the proper identifying values to the previous column names. We now have the two identifiers (ACCT and QTR) and one column that contains all the measures:





For the next step we could create a sub-query with the previous code and then modify that result with a PIVOT clause. However, we can have both a PIVOT and an UNPIVOT clause in the same query.

/* ===== code ===== */     
   
   WITH
     w_data  as
       ( select  11 acct, 23.29 q_1, 81.72 q_2, 16.72 q_3, 60.00 q_4  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  *
     from  w_data
  UNPIVOT  include nulls
           (   m1   FOR   qtr  IN
             ( q_1   as   1
             , q_2   as   2
             , q_3   as   3
             , q_4   as   4
             )
           )
    PIVOT  ( sum(m1) 
           FOR  acct IN
             (  11   as  acct_11
             ,  35   as  acct_35
             ,  44   as  acct_44
             )
           ) 
    order  by 1 ;

Here in the added PIVOT clause we create the three new columns for the three account values. We must use an aggregate function on the measure (sum(m1),  max(m1), ...) because the PIVOT clause turns the query into an aggregate (group-by) query. The QTR column remains, but the ACCT column is replaced by the three, more specific, account columns. The data-set has now been "Inverted":