The data below represents financial information from different months and states. Three different products are shown with type 1 being a profit and type 2 being a loss. There are two measures, a product count, and the total profit or loss.
-----> Example SQL Code Listing-#1:
WITH
w_data as
( select NULL as mn /* Month */
, NULL as st /* State */
, NULL as pd /* Product */
, NULL as tp /* Type */
, NULL as ct /* Count */
, NULL as pf /* Profit */
from dual where 1=0
----------------------------------------------------------------------
union all select '1994-10', 'IL', 'DoDad', 1, 37, 697.45 from dual
union all select '1994-10', 'IL', 'DoDad', 2, 13, -303.03 from dual
union all select '1994-10', 'IL', 'Gizmo', 2, 23, -558.90 from dual
union all select '1994-10', 'IL', 'Gizmo', 2, 21, -468.51 from dual
union all select '1994-10', 'IL', 'Wigit', 1, 69, 867.33 from dual
union all select '1994-10', 'IL', 'Wigit', 2, 24, -494.64 from dual
union all select '1994-10', 'IL', 'Wigit', 2, 4, -65.92 from dual
union all select '1994-10', 'NY', 'DoDad', 1, 71, 1615.96 from dual
union all select '1994-10', 'NY', 'DoDad', 1, 45, 840.60 from dual
union all select '1994-10', 'NY', 'DoDad', 2, 19, -352.64 from dual
union all select '1994-10', 'NY', 'DoDad', 2, 21, -533.19 from dual
union all select '1994-10', 'NY', 'DoDad', 2, 4, -94.28 from dual
union all select '1994-10', 'NY', 'Gizmo', 1, 282, 6426.78 from dual
union all select '1994-10', 'NY', 'Gizmo', 1, 105, 2272.20 from dual
union all select '1994-10', 'NY', 'Gizmo', 1, 221, 4274.14 from dual
union all select '1994-10', 'NY', 'Gizmo', 2, 2, -14.68 from dual
union all select '1994-10', 'NY', 'Wigit', 1, 40, 879.60 from dual
union all select '1994-10', 'NY', 'Wigit', 1, 285, 6047.70 from dual
union all select '1994-11', 'IL', 'DoDad', 1, 196, 2887.08 from dual
union all select '1994-11', 'IL', 'DoDad', 1, 101, 2228.06 from dual
union all select '1994-11', 'IL', 'DoDad', 1, 333, 5471.19 from dual
union all select '1994-11', 'IL', 'DoDad', 2, 7, -75.95 from dual
union all select '1994-11', 'IL', 'DoDad', 2, 25, -419.50 from dual
union all select '1994-11', 'IL', 'Gizmo', 1, 255, 4931.70 from dual
union all select '1994-11', 'IL', 'Gizmo', 2, 23, -423.43 from dual
union all select '1994-11', 'IL', 'Wigit', 1, 36, 700.92 from dual
union all select '1994-11', 'IL', 'Wigit', 1, 329, 6385.89 from dual
union all select '1994-11', 'IL', 'Wigit', 2, 19, -463.79 from dual
union all select '1994-11', 'NY', 'DoDad', 1, 373, 8642.41 from dual
union all select '1994-11', 'NY', 'Gizmo', 1, 21, 476.91 from dual
union all select '1994-11', 'NY', 'Gizmo', 2, 14, -99.40 from dual
union all select '1994-11', 'NY', 'Wigit', 1, 394, 9550.56 from dual
union all select '1994-11', 'NY', 'Wigit', 1, 57, 1046.52 from dual
union all select '1994-11', 'NY', 'Wigit', 1, 133, 2386.02 from dual
union all select '1994-11', 'NY', 'Wigit', 2, 23, -543.49 from dual
union all select '1994-12', 'IL', 'DoDad', 1, 280, 6134.80 from dual
union all select '1994-12', 'IL', 'DoDad', 1, 68, 948.60 from dual
union all select '1994-12', 'IL', 'DoDad', 2, 6, -148.98 from dual
union all select '1994-12', 'IL', 'Gizmo', 1, 252, 5503.68 from dual
union all select '1994-12', 'IL', 'Gizmo', 1, 156, 2322.84 from dual
union all select '1994-12', 'IL', 'Gizmo', 2, 14, -236.60 from dual
union all select '1994-12', 'IL', 'Wigit', 1, 132, 2109.36 from dual
union all select '1994-12', 'IL', 'Wigit', 1, 339, 8041.08 from dual
union all select '1994-12', 'IL', 'Wigit', 1, 197, 3520.39 from dual
union all select '1994-12', 'IL', 'Wigit', 2, 20, -341.60 from dual
union all select '1994-12', 'IL', 'Wigit', 2, 4, -32.20 from dual
union all select '1994-12', 'NY', 'DoDad', 1, 308, 5528.60 from dual
union all select '1994-12', 'NY', 'DoDad', 1, 335, 6673.20 from dual
union all select '1994-12', 'NY', 'DoDad', 1, 109, 2237.77 from dual
union all select '1994-12', 'NY', 'DoDad', 2, 17, -326.91 from dual
union all select '1994-12', 'NY', 'DoDad', 2, 4, -83.32 from dual
union all select '1994-12', 'NY', 'DoDad', 2, 4, -51.04 from dual
union all select '1994-12', 'NY', 'Wigit', 1, 258, 3397.86 from dual
union all select '1994-12', 'NY', 'Wigit', 1, 23, 302.91 from dual
union all select '1994-12', 'NY', 'Wigit', 2, 9, -170.73 from dual
union all select '1994-12', 'NY', 'Gizmo', 1, 296, 3797.68 from dual
union all select '1994-12', 'NY', 'Gizmo', 2, 17, -131.41 from dual
union all select '1994-12', 'NY', 'Gizmo', 2, 17, -158.78 from dual
union all select '1994-12', 'NY', 'Gizmo', 2, 1, -13.38 from dual
----------------------------------------------------------------------
)
select *
from w_data ;
So, here's the pivot query. The "FOR" keyword precedes the two columns that contain the values that will be used to determine the pivot groups. Then, following the "IN" keyword, are the list of those values and the name of each pivot-group. Notice that there are two measures and that each pivot-group will include both measures to create twelve (6 x 2 = 12) new columns. This query displays the month, state, and then all twelve new pivot-columns.
-----> Example SQL Code Listing-#2:
WITH
w_data as
( select NULL as mn /* Month */
, NULL as st /* State */
, NULL as pd /* Product */
, NULL as tp /* Type */
, NULL as ct /* Count */
, NULL as pf /* Profit */
from dual where 1=0
----------------------------------------------------------------------
union all select '1994-10', 'IL', 'DoDad', 1, 37, 697.45 from dual
union all select '1994-10', 'IL', 'DoDad', 2, 13, -303.03 from dual
union all select '1994-10', 'IL', 'Gizmo', 2, 23, -558.90 from dual
union all select '1994-10', 'IL', 'Gizmo', 2, 21, -468.51 from dual
union all select '1994-10', 'IL', 'Wigit', 1, 69, 867.33 from dual
union all select '1994-10', 'IL', 'Wigit', 2, 24, -494.64 from dual
union all select '1994-10', 'IL', 'Wigit', 2, 4, -65.92 from dual
union all select '1994-10', 'NY', 'DoDad', 1, 71, 1615.96 from dual
union all select '1994-10', 'NY', 'DoDad', 1, 45, 840.60 from dual
union all select '1994-10', 'NY', 'DoDad', 2, 19, -352.64 from dual
union all select '1994-10', 'NY', 'DoDad', 2, 21, -533.19 from dual
union all select '1994-10', 'NY', 'DoDad', 2, 4, -94.28 from dual
union all select '1994-10', 'NY', 'Gizmo', 1, 282, 6426.78 from dual
union all select '1994-10', 'NY', 'Gizmo', 1, 105, 2272.20 from dual
union all select '1994-10', 'NY', 'Gizmo', 1, 221, 4274.14 from dual
union all select '1994-10', 'NY', 'Gizmo', 2, 2, -14.68 from dual
union all select '1994-10', 'NY', 'Wigit', 1, 40, 879.60 from dual
union all select '1994-10', 'NY', 'Wigit', 1, 285, 6047.70 from dual
union all select '1994-11', 'IL', 'DoDad', 1, 196, 2887.08 from dual
union all select '1994-11', 'IL', 'DoDad', 1, 101, 2228.06 from dual
union all select '1994-11', 'IL', 'DoDad', 1, 333, 5471.19 from dual
union all select '1994-11', 'IL', 'DoDad', 2, 7, -75.95 from dual
union all select '1994-11', 'IL', 'DoDad', 2, 25, -419.50 from dual
union all select '1994-11', 'IL', 'Gizmo', 1, 255, 4931.70 from dual
union all select '1994-11', 'IL', 'Gizmo', 2, 23, -423.43 from dual
union all select '1994-11', 'IL', 'Wigit', 1, 36, 700.92 from dual
union all select '1994-11', 'IL', 'Wigit', 1, 329, 6385.89 from dual
union all select '1994-11', 'IL', 'Wigit', 2, 19, -463.79 from dual
union all select '1994-11', 'NY', 'DoDad', 1, 373, 8642.41 from dual
union all select '1994-11', 'NY', 'Gizmo', 1, 21, 476.91 from dual
union all select '1994-11', 'NY', 'Gizmo', 2, 14, -99.40 from dual
union all select '1994-11', 'NY', 'Wigit', 1, 394, 9550.56 from dual
union all select '1994-11', 'NY', 'Wigit', 1, 57, 1046.52 from dual
union all select '1994-11', 'NY', 'Wigit', 1, 133, 2386.02 from dual
union all select '1994-11', 'NY', 'Wigit', 2, 23, -543.49 from dual
union all select '1994-12', 'IL', 'DoDad', 1, 280, 6134.80 from dual
union all select '1994-12', 'IL', 'DoDad', 1, 68, 948.60 from dual
union all select '1994-12', 'IL', 'DoDad', 2, 6, -148.98 from dual
union all select '1994-12', 'IL', 'Gizmo', 1, 252, 5503.68 from dual
union all select '1994-12', 'IL', 'Gizmo', 1, 156, 2322.84 from dual
union all select '1994-12', 'IL', 'Gizmo', 2, 14, -236.60 from dual
union all select '1994-12', 'IL', 'Wigit', 1, 132, 2109.36 from dual
union all select '1994-12', 'IL', 'Wigit', 1, 339, 8041.08 from dual
union all select '1994-12', 'IL', 'Wigit', 1, 197, 3520.39 from dual
union all select '1994-12', 'IL', 'Wigit', 2, 20, -341.60 from dual
union all select '1994-12', 'IL', 'Wigit', 2, 4, -32.20 from dual
union all select '1994-12', 'NY', 'DoDad', 1, 308, 5528.60 from dual
union all select '1994-12', 'NY', 'DoDad', 1, 335, 6673.20 from dual
union all select '1994-12', 'NY', 'DoDad', 1, 109, 2237.77 from dual
union all select '1994-12', 'NY', 'DoDad', 2, 17, -326.91 from dual
union all select '1994-12', 'NY', 'DoDad', 2, 4, -83.32 from dual
union all select '1994-12', 'NY', 'DoDad', 2, 4, -51.04 from dual
union all select '1994-12', 'NY', 'Wigit', 1, 258, 3397.86 from dual
union all select '1994-12', 'NY', 'Wigit', 1, 23, 302.91 from dual
union all select '1994-12', 'NY', 'Wigit', 2, 9, -170.73 from dual
union all select '1994-12', 'NY', 'Gizmo', 1, 296, 3797.68 from dual
union all select '1994-12', 'NY', 'Gizmo', 2, 17, -131.41 from dual
union all select '1994-12', 'NY', 'Gizmo', 2, 17, -158.78 from dual
union all select '1994-12', 'NY', 'Gizmo', 2, 1, -13.38 from dual
----------------------------------------------------------------------
)
select mn, st
, dodad_p_cnt, gizmo_p_cnt, wigit_p_cnt
, dodad_p_amt, gizmo_p_amt, wigit_p_amt
, dodad_l_cnt, gizmo_l_cnt, wigit_l_cnt
, dodad_l_amt, gizmo_l_amt, wigit_l_amt
from w_data
PIVOT ( sum(ct) as cnt
, sum(pf) as amt
FOR ( pd, tp )
IN ( ( 'DoDad', 1 ) as dodad_p
, ( 'DoDad', 2 ) as dodad_l
, ( 'Gizmo', 1 ) as gizmo_p
, ( 'Gizmo', 2 ) as gizmo_l
, ( 'Wigit', 1 ) as wigit_p
, ( 'Wigit', 2 ) as wigit_l
)
)
order by mn, st
;
And here is the result:
For each month and state's aggregated output row, all the input rows that match the values for a particular pivot-group are grouped together to find the measures. For example, If you look at the last 3 lines of input data they will all aggregate into the December and New York output line (the last one in the result). Actually the last 13 lines of input data will aggregate into the last row of the result; however, The last 3 lines are also part of the "('Gizmo', 2)" pivot-group so they will show up only in the Gizmo-Loss columns. The counts and amounts of these 3 last lines are summed up and show up on the last line of the result. The counts total up to 35 and show up as shown by the red arrow; and the amounts total up to a 303.57 loss and show up as shown by the green arrow.
No comments:
Post a Comment