February 9, 2011 at 6:59 pm
I hope this is right forum.
I am having a hard time understading new syntax T-SQL 2K5+ Pivots. In Oracle, I select pivoted and non-pivoted(aggregated) in SELECT CLAUSE ANd put the non-aggregate columns in a GROUP BY. All the examples I find on the internet are agregating only one column. Can someone help me convert this PL/SQL script?
ABOUT REPORT:
This is weekly PRoduct List Report(P.L.U.) by for business Period 2, rolled up to district, and company. Weeks 3,4,5 are zeroed out because they have not occured.
NOTE
1) I realize there I am getting extra rollup rows i nthe final output. for instance, Record 3 and 4 are alike except for the Storenbr Column. Rows 1 and 2 which are PLUs for store6504 should rollup up to disrtirct, row 4, with Murdock in STorenbr column. But I'm not necessarily asking for help troubleshooting script.
2) Districtname, and regionname are only included to help me visualize complex decode statements.
WITH dummydata AS
(
SELECT '0177' AS storenbr, 2 AS period, 1 AS weeknbr, '40' AS PLU, 'tomato' as descrip, 16 AS sold, 100 AS total from dual UNION ALL
SELECT '0177' AS storenbr, 2 AS period, 1 AS weeknbr, '50' AS PLU, 'bacon' as descrip, 87 AS sold, 100 AS total from dual UNION ALL
SELECT '0177' AS storenbr, 2 AS period, 2 AS weeknbr, '40' AS PLU, 'tomato' as descrip, 26 AS sold, 100 AS total from dual UNION ALL
SELECT '0177' AS storenbr, 2 AS period, 2 AS weeknbr, '50' AS PLU, 'bacon' as descrip, 97 AS sold, 100 AS total from dual UNION ALL
SELECT '0277' AS storenbr, 2 AS period, 1 AS weeknbr, '40' AS PLU, 'tomato' as descrip, 16 AS sold, 100 AS total from dual UNION ALL
SELECT '0277' AS storenbr, 2 AS period, 1 AS weeknbr, '50' AS PLU, 'bacon' as descrip, 87 AS sold, 100 AS total from dual UNION ALL
SELECT '0277' AS storenbr, 2 AS period, 2 AS weeknbr, '40' AS PLU, 'tomato' as descrip, 26 AS sold, 100 AS total from dual UNION ALL
SELECT '0277' AS storenbr, 2 AS period, 2 AS weeknbr, '50' AS PLU, 'bacon' as descrip, 97 AS sold, 100 AS total from dual UNION ALL
SELECT '6504' AS storenbr, 2 AS period, 1 AS weeknbr, '40' AS PLU, 'tomato' as descrip, 18 AS sold, 100 AS total from dual UNION ALL
SELECT '6504' AS storenbr, 2 AS period, 1 AS weeknbr, '50' AS PLU, 'bacon' as descrip, 89 AS sold, 100 AS total from dual UNION ALL
SELECT '6504' AS storenbr, 2 AS period, 2 AS weeknbr, '40' AS PLU, 'tomato' as descrip, 28 AS sold, 100 AS total from dual UNION ALL
SELECT '6504' AS storenbr, 2 AS period, 2 AS weeknbr, '50' AS PLU, 'bacon' as descrip, 99 AS sold, 100 AS total from dual
)
, store_details as
(
SELECT '0177' AS storenbr, 'Bingham' as districtname, 'R-15 James' as regionname, 'C-Atlantic' as companyname from dual UNION ALL
SELECT '0277' AS storenbr, 'Dunley' as districtname, 'R-15 James' as regionname, 'C-Atlantic' as companyname from dual UNION ALL
SELECT '6504' AS storenbr, 'Murdoc' as districtname, 'R-16 Reynolds' as regionname, 'C-Soutn' as companyname from dual
)
, pivoted as
(
select storenbr
, plu
, max(descrip) as Descrip
, max(decode(dd.weeknbr, 1, sold , 0)) as Week1Sold
, max(decode(dd.weeknbr, 1, total , 0)) as Week1total
, max(decode(dd.weeknbr, 2, sold , 0)) as Week2Sold
, max(decode(dd.weeknbr, 2, total , 0)) as Week2total
, max(decode(dd.weeknbr, 3, sold , 0)) as Week3Sold
, max(decode(dd.weeknbr, 3, total , 0)) as Week3total
, max(decode(dd.weeknbr, 4, sold , 0)) as Week4Sold
, max(decode(dd.weeknbr, 4, total , 0)) as Week4total
, max(decode(dd.weeknbr, 5, sold , 0)) as Week5Sold
, max(decode(dd.weeknbr, 5, total , 0)) as Week5total
from dummydata dd
group by storenbr, plu
order by storenbr, plu
)
select decode(grouping(companyname), 0, companyname, 'GRAND') as companyname
, decode(grouping(regionname), 0, regionname, decode(grouping(companyname), 0, companyname, 'GRAND')) as regionname
, decode(grouping(districtname), 0, districtname, decode(grouping(regionname), 0, regionname, decode(grouping(companyname), 0, companyname, 'GRAND'))) as districtname
, decode(grouping(stores.storenbr), 0, stores.storenbr, decode(grouping(districtname), 0, districtname, decode(grouping(regionname), 0, regionname, decode(grouping(companyname), 0, companyname, 'GRAND')))) as storenbr
, decode(grouping(plu), 0, plu, decode(grouping(districtname), 0, 'DISTRICT-PLU', decode(grouping(regionname), 0, 'REGION-PLU', decode(grouping(companyname), 0, 'COMPANY', 'GRAND')))) as plu
, decode(grouping(plu), 0, max(descrip), 'TOTALS') as description
, sum(Week1Sold) as Week1Sold
, sum(Week1total) as Week1total
, sum(Week2Sold) as Week2Sold
, sum(Week2total) as Week2total
, sum(Week3Sold) as Week3Sold
, sum(Week3total) as Week3total
, sum(Week4Sold) as Week4Sold
, sum(Week4total) as Week4total
, sum(Week5Sold) as Week5Sold
, sum(Week5total) as Week5total
from pivoted pvt
inner join store_details stores on pvt.storenbr = stores.storenbr
group by rollup (companyname, regionname, districtname, stores.storenbr, PLU )
COMPANYNAME REGIONNAME DISTRICTNAME STORENBR PLU DESCRIPTION WEEK1SOLD WEEK1TOTAL WEEK2SOLD WEEK2TOTAL WEEK3SOLD WEEK3TOTAL WEEK4SOLD WEEK4TOTAL WEEK5SOLD WEEK5TOTAL
----------- ------------- ------------- ------------- ------------ ----------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
C-Soutn R-16 Reynolds Murdoc 6504 40 tomato 18 100 28 100 0 0 0 0 0 0
C-Soutn R-16 Reynolds Murdoc 6504 50 bacon 89 100 99 100 0 0 0 0 0 0
C-Soutn R-16 Reynolds Murdoc 6504 DISTRICT-PLU TOTALS 107 200 127 200 0 0 0 0 0 0
C-Soutn R-16 Reynolds Murdoc Murdoc DISTRICT-PLU TOTALS 107 200 127 200 0 0 0 0 0 0
C-Soutn R-16 Reynolds R-16 Reynolds R-16 Reynolds REGION-PLU TOTALS 107 200 127 200 0 0 0 0 0 0
C-Soutn C-Soutn C-Soutn C-Soutn COMPANY TOTALS 107 200 127 200 0 0 0 0 0 0
C-Atlantic R-15 James Dunley 0277 40 tomato 16 100 26 100 0 0 0 0 0 0
C-Atlantic R-15 James Dunley 0277 50 bacon 87 100 97 100 0 0 0 0 0 0
C-Atlantic R-15 James Dunley 0277 DISTRICT-PLU TOTALS 103 200 123 200 0 0 0 0 0 0
C-Atlantic R-15 James Dunley Dunley DISTRICT-PLU TOTALS 103 200 123 200 0 0 0 0 0 0
C-Atlantic R-15 James Bingham 0177 40 tomato 16 100 26 100 0 0 0 0 0 0
C-Atlantic R-15 James Bingham 0177 50 bacon 87 100 97 100 0 0 0 0 0 0
C-Atlantic R-15 James Bingham 0177 DISTRICT-PLU TOTALS 103 200 123 200 0 0 0 0 0 0
C-Atlantic R-15 James Bingham Bingham DISTRICT-PLU TOTALS 103 200 123 200 0 0 0 0 0 0
C-Atlantic R-15 James R-15 James R-15 James REGION-PLU TOTALS 206 400 246 400 0 0 0 0 0 0
C-Atlantic C-Atlantic C-Atlantic C-Atlantic COMPANY TOTALS 206 400 246 400 0 0 0 0 0 0
GRAND GRAND GRAND GRAND GRAND TOTALS 313 600 373 600 0 0 0 0 0 0
;
February 10, 2011 at 3:01 am
I renamed and removed some columns. This should now be 100% clearer. There is no problem with the data as I stated in the first post.
So question is how do I convert to T-SQL?
WITH dummydata AS
(
SELECT 'Store1' AS storenbr, 2 AS period, 1 AS weeknbr, '40' AS PLU, 'tomato' as descrip, 16 AS used, 100 AS wasted from dual UNION ALL
SELECT 'Store1' AS storenbr, 2 AS period, 1 AS weeknbr, '50' AS PLU, 'bacon' as descrip, 87 AS used, 100 AS wasted from dual UNION ALL
SELECT 'Store1' AS storenbr, 2 AS period, 2 AS weeknbr, '40' AS PLU, 'tomato' as descrip, 26 AS used, 100 AS wasted from dual UNION ALL
SELECT 'Store1' AS storenbr, 2 AS period, 2 AS weeknbr, '50' AS PLU, 'bacon' as descrip, 97 AS used, 100 AS wasted from dual UNION ALL
SELECT 'Store2' AS storenbr, 2 AS period, 1 AS weeknbr, '40' AS PLU, 'tomato' as descrip, 16 AS used, 100 AS wasted from dual UNION ALL
SELECT 'Store2' AS storenbr, 2 AS period, 1 AS weeknbr, '50' AS PLU, 'bacon' as descrip, 87 AS used, 100 AS wasted from dual UNION ALL
SELECT 'Store2' AS storenbr, 2 AS period, 2 AS weeknbr, '40' AS PLU, 'tomato' as descrip, 26 AS used, 100 AS wasted from dual UNION ALL
SELECT 'Store2' AS storenbr, 2 AS period, 2 AS weeknbr, '50' AS PLU, 'bacon' as descrip, 97 AS used, 100 AS wasted from dual UNION ALL
SELECT 'Store3' AS storenbr, 2 AS period, 1 AS weeknbr, '40' AS PLU, 'tomato' as descrip, 18 AS used, 100 AS wasted from dual UNION ALL
SELECT 'Store3' AS storenbr, 2 AS period, 1 AS weeknbr, '50' AS PLU, 'bacon' as descrip, 89 AS used, 100 AS wasted from dual UNION ALL
SELECT 'Store3' AS storenbr, 2 AS period, 2 AS weeknbr, '40' AS PLU, 'tomato' as descrip, 28 AS used, 100 AS wasted from dual UNION ALL
SELECT 'Store3' AS storenbr, 2 AS period, 2 AS weeknbr, '50' AS PLU, 'bacon' as descrip, 99 AS used, 100 AS wasted from dual
)
, store_details as
(
SELECT 'Store3' AS storenbr, 'D-Bingham' as districtname, 'R-15 James' as regionname, 'C-Atlantic' as companyname from dual UNION ALL
SELECT 'Store2' AS storenbr, 'D-Dunley' as districtname, 'R-15 James' as regionname, 'C-Atlantic' as companyname from dual UNION ALL
SELECT 'Store1' AS storenbr, 'D-Murdoc' as districtname, 'R-16 Reynolds' as regionname, 'C-Soutn' as companyname from dual
)
, pivoted as
(
select storenbr
, plu
, max(descrip) as Descrip
, max(decode(dd.weeknbr, 1, used , 0)) as Week1used
, max(decode(dd.weeknbr, 1, wasted , 0)) as Week1wasted
, max(decode(dd.weeknbr, 2, used , 0)) as Week2used
, max(decode(dd.weeknbr, 2, wasted , 0)) as Week2wasted
, max(decode(dd.weeknbr, 3, used , 0)) as Week3used
, max(decode(dd.weeknbr, 3, wasted , 0)) as Week3wasted
, max(decode(dd.weeknbr, 4, used , 0)) as Week4used
, max(decode(dd.weeknbr, 4, wasted , 0)) as Week4wasted
, max(decode(dd.weeknbr, 5, used , 0)) as Week5used
, max(decode(dd.weeknbr, 5, wasted , 0)) as Week5wasted
from dummydata dd
group by storenbr, plu
order by storenbr, plu
)
select decode(grouping(stores.storenbr), 0, stores.storenbr, decode(grouping(districtname), 0, districtname, decode(grouping(regionname), 0, regionname, decode(grouping(companyname), 0, companyname, 'GRAND')))) as storenbr
, decode(grouping(descrip), 0, descrip, 'TOTAL') as descrip
--== I substited Description for clarity
, sum(Week1used) as Week1used
, sum(Week1wasted) as Week1wasted
, sum(Week2used) as Week2used
, sum(Week2wasted) as Week2wasted
, sum(Week3used) as Week3used
, sum(Week3wasted) as Week3wasted
, sum(Week4used) as Week4used
, sum(Week4wasted) as Week4wasted
, sum(Week5used) as Week5used
, sum(Week5wasted) as Week5wasted
, companyname
from pivoted pvt
inner join store_details stores on pvt.storenbr = stores.storenbr
group by companyname
, rollup ( regionname
, districtname
, stores.storenbr
, descrip
)
;
New output:
STORENBR DESCRIP WEEK1USED WEEK1WASTED WEEK2USED WEEK2WASTED WEEK3USED WEEK3WASTED WEEK4USED WEEK4WASTED WEEK5USED WEEK5WASTED COMPANYNAME
------------- ------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- -----------
Store1 bacon 87 100 97 100 0 0 0 0 0 0 C-Soutn
Store1 tomato 16 100 26 100 0 0 0 0 0 0 C-Soutn
Store1 TOTAL 103 200 123 200 0 0 0 0 0 0 C-Soutn
D-Murdoc TOTAL 103 200 123 200 0 0 0 0 0 0 C-Soutn
R-16 Reynolds TOTAL 103 200 123 200 0 0 0 0 0 0 C-Soutn
C-Soutn TOTAL 103 200 123 200 0 0 0 0 0 0 C-Soutn
Store2 bacon 87 100 97 100 0 0 0 0 0 0 C-Atlantic
Store2 tomato 16 100 26 100 0 0 0 0 0 0 C-Atlantic
Store2 TOTAL 103 200 123 200 0 0 0 0 0 0 C-Atlantic
D-Dunley TOTAL 103 200 123 200 0 0 0 0 0 0 C-Atlantic
Store3 bacon 89 100 99 100 0 0 0 0 0 0 C-Atlantic
Store3 tomato 18 100 28 100 0 0 0 0 0 0 C-Atlantic
Store3 TOTAL 107 200 127 200 0 0 0 0 0 0 C-Atlantic
D-Bingham TOTAL 107 200 127 200 0 0 0 0 0 0 C-Atlantic
R-15 James TOTAL 210 400 250 400 0 0 0 0 0 0 C-Atlantic
C-Atlantic TOTAL 210 400 250 400 0 0 0 0 0 0 C-Atlantic
[Edited to add explanation of the DECODE/Grouping Technique in the main SELECT ]
About the complex Grouping statement:
I used the GROUPING Function and DECODE statement to let my storenbr column reflect the rollup level.
The DECODE tells the query to use the value in the column in thenext llevel up instead of outputing NULL.
The GROUPING function tells the query when the rollup occurs. See the storenbr column is never blank.
(There is no rollup on companyname, so no need to place in rollup clause. )
Feel free to offer better worded explanation.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply