Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Need help converting Oracle PIVOT script to SQL Server

740383Feb 11 2011
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  

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 11 2011
Added on Feb 11 2011
0 comments
957 views