0 Replies Latest reply: Feb 11, 2011 9:14 PM by 740383 RSS

    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