Need help converting Oracle PIVOT script to SQL Server

740383

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