I hope it is not inappropriate to ask for SQL Server question in a .NET forum. I can not understand SQl 2k5+ syntax for pivoting. I am pivoting on company name and storenbr in the following. Thanks for any help.
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