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":
