/* ===== code ===== */
WITH
w_data as
( select 'NY' st, 1 rgn, 13 q1cnt, 245.25 q1amt, 13 q2cnt, 245.25 q2amt, 13 q3cnt, 245.25 q3amt, 13 q4cnt, 245.25 q4amt from dual union all
select 'NY' , 2 , 43 , 760.34 , 38 , 842.11 , 39 , 425.98 , 54 , 571.53 from dual union all
select 'NY' , 3 , 34 , 743.21 , 38 , 627.24 , null , 463.74 , 41 , 154.96 from dual union all
select 'HI' , 1 , 14 , 256.13 , 11 , 532.48 , 10 , 246.37 , 18 , 261.37 from dual union all
select 'OR' , 1 , 73 , 758.14 , 71 , 132.84 , 63 , 623.74 , 82 , 851.92 from dual union all
select 'OR' , 2 , null , null , 62 , 275.11 , 73 , 846.32 , 70 , 253.19 from dual union all
select 'UT' , 1 , 27 , 156.38 , 32 , 637.19 , 31 , 256.48 , 39 , 146.75 from dual union all
select 'UT' , 2 , 24 , 264.82 , 20 , 423.61 , 21 , null , 18 , 367.88 from dual union all
select 'MT' , 1 , 3 , 374.28 , 8 , 125.35 , 5 , 423.66 , 7 , 536.14 from dual
)
select * from w_data ;
Here we have a couple of identifying columns (State and Region) that don't really have anything to do with the Un-Pivot -- they will just need to be associated with any measure that appears in their row. We also have eight columns of measures, two measures for each quarter of the year (Count and Amount). Un-Pivoting the data could mean that we take all these measures and un-pivot them into one single measures column, or it could mean that we un-pivot the four "Count" columns into one measures column and the four "Amount" columns into another measures column.
For the first example, let's start with that last one mentioned -- we'll un-pivot the data into two measure columns. We'll have a single "CNT" column and a seperate "AMT" column for the measures. We'll also create a new "QTR" colum that will contain an identifier that tells us which quarter the two measures came from.
Here is the solution. See the explanation and results below:
/* ===== code ===== */
WITH
w_data as
( select 'NY' st, 1 rgn, 13 q1cnt, 245.25 q1amt, 13 q2cnt, 245.25 q2amt, 13 q3cnt, 245.25 q3amt, 13 q4cnt, 245.25 q4amt from dual union all
select 'NY' , 2 , 43 , 760.34 , 38 , 842.11 , 39 , 425.98 , 54 , 571.53 from dual union all
select 'NY' , 3 , 34 , 743.21 , 38 , 627.24 , null , 463.74 , 41 , 154.96 from dual union all
select 'HI' , 1 , 14 , 256.13 , 11 , 532.48 , 10 , 246.37 , 18 , 261.37 from dual union all
select 'OR' , 1 , 73 , 758.14 , 71 , 132.84 , 63 , 623.74 , 82 , 851.92 from dual union all
select 'OR' , 2 , null , null , 62 , 275.11 , 73 , 846.32 , 70 , 253.19 from dual union all
select 'UT' , 1 , 27 , 156.38 , 32 , 637.19 , 31 , 256.48 , 39 , 146.75 from dual union all
select 'UT' , 2 , 24 , 264.82 , 20 , 423.61 , 21 , null , 18 , 367.88 from dual union all
select 'MT' , 1 , 3 , 374.28 , 8 , 125.35 , 5 , 423.66 , 7 , 536.14 from dual
)
select st, rgn
, qtr
, cnt, amt
from w_data
UNPIVOT include nulls
( ( cnt, amt ) FOR qtr IN
( ( q1cnt, q1amt ) as 1
, ( q2cnt, q2amt ) as 2
, ( q3cnt, q3amt ) as 3
, ( q4cnt, q4amt ) as 4
)
)
--where nvl( cnt, 0 ) < 30
order by 1, 2, 3 ;
Explanation:
The "UNPIVOT" clause is placed immediately after the "FROM" clause. If you have a "WHERE" clause it will follow the "UNPIVOT" clause as shown.
The "Include Nulls" phrase will include an output row even if all the newly created measure columns are NULL. Take these two words out of the query and observe how the result-set changes.
Inside the "UNPIVOT" clause we start by creating the set of new measure columns (CNT and AMT). Notice that because we are creating a set (more than one) of new measures we put them in parentesis. These columns will contain all the un-pivoted measures. After the keyword "FOR" we create a new column (QTR) that will contain an identifier that tells us which quarter the measures came from (which original set of columns the specific count and amount measures came from).
After the "IN" keyword we include a parenthesis enclosed list that associates each set of original measure columns with an identifying value. For example, the original columns (Q1CNT and Q1AMT) are assigned the identifier of "1" -- this is the value that will appear in the new QTR column allong side the measures from those two columns.
And, one last item, if you un-comment (remove the "--" from the beginning of) the where clause, then the result-set will filter out any records that have a CNT of 30 or greater.
Notice that in the following example, all eight measure columns are un-pivoted into one single measure column (VAL). The new "VAL_ID" column will contain a text identifier that not only tells which quarter the measure comes from, but also tells us whether it is a "Count" or an "Amount" value.
/* ===== code ===== */
WITH
w_data as
( select 'NY' st, 1 rgn, 13 q1cnt, 245.25 q1amt, 13 q2cnt, 245.25 q2amt, 13 q3cnt, 245.25 q3amt, 13 q4cnt, 245.25 q4amt from dual union all
select 'NY' , 2 , 43 , 760.34 , 38 , 842.11 , 39 , 425.98 , 54 , 571.53 from dual union all
select 'NY' , 3 , 34 , 743.21 , 38 , 627.24 , null , 463.74 , 41 , 154.96 from dual union all
select 'HI' , 1 , 14 , 256.13 , 11 , 532.48 , 10 , 246.37 , 18 , 261.37 from dual union all
select 'OR' , 1 , 73 , 758.14 , 71 , 132.84 , 63 , 623.74 , 82 , 851.92 from dual union all
select 'OR' , 2 , null , null , 62 , 275.11 , 73 , 846.32 , 70 , 253.19 from dual union all
select 'UT' , 1 , 27 , 156.38 , 32 , 637.19 , 31 , 256.48 , 39 , 146.75 from dual union all
select 'UT' , 2 , 24 , 264.82 , 20 , 423.61 , 21 , null , 18 , 367.88 from dual union all
select 'MT' , 1 , 3 , 374.28 , 8 , 125.35 , 5 , 423.66 , 7 , 536.14 from dual
)
select st, rgn
, val_id, val
from w_data
UNPIVOT include nulls
( val FOR val_id IN
( q1cnt as 'Qtr-1 Count'
, q2cnt as 'Qtr-2 Count'
, q3cnt as 'Qtr-3 Count'
, q4cnt as 'Qtr-4 Count'
, q1amt as 'Qtr-1 Amount'
, q2amt as 'Qtr-2 Amount'
, q3amt as 'Qtr-3 Amount'
, q4amt as 'Qtr-4 Amount'
)
)
order by 1, 2, 3 ;
/* --- Partial Results --- */
-- ST RGN VAL_ID VAL
-- -- --- ------------ -------
-- HI 1 Qtr-1 Amount 256.13
-- HI 1 Qtr-1 Count 14
-- HI 1 Qtr-2 Amount 532.48
-- HI 1 Qtr-2 Count 11
-- HI 1 Qtr-3 Amount 246.37
-- HI 1 Qtr-3 Count 10
-- HI 1 Qtr-4 Amount 261.37
-- HI 1 Qtr-4 Count 18
-- MT 1 Qtr-1 Amount 374.28
-- MT 1 Qtr-1 Count 3
-- ...
This next example is very similar to the last one. In this query, there are two identifying columns created (VAL_QTR and VAL_TYP) -- the identifying information here is split between these two columns. The first tells us which quarter the measure came from, and the second column tells us whether it was a "Count" or an "Amount" value.
/* ===== code ===== */
WITH
w_data as
( select 'NY' st, 1 rgn, 13 q1cnt, 245.25 q1amt, 13 q2cnt, 245.25 q2amt, 13 q3cnt, 245.25 q3amt, 13 q4cnt, 245.25 q4amt from dual union all
select 'NY' , 2 , 43 , 760.34 , 38 , 842.11 , 39 , 425.98 , 54 , 571.53 from dual union all
select 'NY' , 3 , 34 , 743.21 , 38 , 627.24 , null , 463.74 , 41 , 154.96 from dual union all
select 'HI' , 1 , 14 , 256.13 , 11 , 532.48 , 10 , 246.37 , 18 , 261.37 from dual union all
select 'OR' , 1 , 73 , 758.14 , 71 , 132.84 , 63 , 623.74 , 82 , 851.92 from dual union all
select 'OR' , 2 , null , null , 62 , 275.11 , 73 , 846.32 , 70 , 253.19 from dual union all
select 'UT' , 1 , 27 , 156.38 , 32 , 637.19 , 31 , 256.48 , 39 , 146.75 from dual union all
select 'UT' , 2 , 24 , 264.82 , 20 , 423.61 , 21 , null , 18 , 367.88 from dual union all
select 'MT' , 1 , 3 , 374.28 , 8 , 125.35 , 5 , 423.66 , 7 , 536.14 from dual
)
select st, rgn
, val_qtr, val_typ, val
from w_data
UNPIVOT include nulls
( val FOR ( val_qtr, val_typ ) IN
( q1cnt as ( 'Qtr-1', 'Count' )
, q2cnt as ( 'Qtr-2', 'Count' )
, q3cnt as ( 'Qtr-3', 'Count' )
, q4cnt as ( 'Qtr-4', 'Count' )
, q1amt as ( 'Qtr-1', 'Amount' )
, q2amt as ( 'Qtr-2', 'Amount' )
, q3amt as ( 'Qtr-3', 'Amount' )
, q4amt as ( 'Qtr-4', 'Amount' )
)
)
order by 1, 2, 3, 4 ;
/* --- Partial Results --- */
-- ST RGN VAL_QTR VAL_TYP VAL
-- -- --- ------- ------- -------
-- HI 1 Qtr-1 Amount 256.13
-- HI 1 Qtr-1 Count 14
-- HI 1 Qtr-2 Amount 532.48
-- HI 1 Qtr-2 Count 11
-- HI 1 Qtr-3 Amount 246.37
-- HI 1 Qtr-3 Count 10
-- HI 1 Qtr-4 Amount 261.37
-- HI 1 Qtr-4 Count 18
-- MT 1 Qtr-1 Amount 374.28
-- MT 1 Qtr-1 Count 3
-- ...
This final example shows us that we can create as many identifying columns as we wish to include; and that we can define their values to just about anything we would need.
/* ===== code ===== */
WITH
w_data as
( select 'NY' st, 1 rgn, 13 q1cnt, 245.25 q1amt, 13 q2cnt, 245.25 q2amt, 13 q3cnt, 245.25 q3amt, 13 q4cnt, 245.25 q4amt from dual union all
select 'NY' , 2 , 43 , 760.34 , 38 , 842.11 , 39 , 425.98 , 54 , 571.53 from dual union all
select 'NY' , 3 , 34 , 743.21 , 38 , 627.24 , null , 463.74 , 41 , 154.96 from dual union all
select 'HI' , 1 , 14 , 256.13 , 11 , 532.48 , 10 , 246.37 , 18 , 261.37 from dual union all
select 'OR' , 1 , 73 , 758.14 , 71 , 132.84 , 63 , 623.74 , 82 , 851.92 from dual union all
select 'OR' , 2 , null , null , 62 , 275.11 , 73 , 846.32 , 70 , 253.19 from dual union all
select 'UT' , 1 , 27 , 156.38 , 32 , 637.19 , 31 , 256.48 , 39 , 146.75 from dual union all
select 'UT' , 2 , 24 , 264.82 , 20 , 423.61 , 21 , null , 18 , 367.88 from dual union all
select 'MT' , 1 , 3 , 374.28 , 8 , 125.35 , 5 , 423.66 , 7 , 536.14 from dual
)
select st, rgn
, qtr, qnm, qrn, qwrd, qna, qnb
, cnt, amt
from w_data
UNPIVOT include nulls
( ( cnt, amt ) FOR ( qtr , qnm , qrn , qwrd , qna , qnb ) IN
( ( q1cnt, q1amt ) as ( 1 , 'Quarter-1' , 'I' , 'One' , 0.00 , 0.25 )
, ( q2cnt, q2amt ) as ( 2 , 'Quarter-2' , 'II' , 'Two' , 0.25 , 0.50 )
, ( q3cnt, q3amt ) as ( 3 , 'Quarter-3' , 'III' , 'Three' , 0.50 , 0.75 )
, ( q4cnt, q4amt ) as ( 4 , 'Quarter-4' , 'IV' , 'Four' , 0.75 , 1.00 )
)
)
order by 1, 2, 3 ;
/* --- Partial Results --- */
-- ST RGN QTR QNM QRN QWRD QNA QNB CNT AMT
-- -- --- --- --------- --- ----- ----- ----- --- -------
-- HI 1 1 Quarter-1 I One 0 0.25 14 256.13
-- HI 1 2 Quarter-2 II Two 0.25 0.5 11 532.48
-- HI 1 3 Quarter-3 III Three 0.5 0.75 10 246.37
-- HI 1 4 Quarter-4 IV Four 0.75 1 18 261.37
-- MT 1 1 Quarter-1 I One 0 0.25 3 374.28
-- MT 1 2 Quarter-2 II Two 0.25 0.5 8 125.35
-- ...
No comments:
Post a Comment