11 Replies Latest reply on Feb 11, 2010 10:54 PM by 489554

    Help with PIVOT query (or advice on best way to do this)

    489554
      Hi SQL gurus,

      I would appreciate any assistance you could provide on this query, I'm assuming the easiest way to do this would be using the PIVOT function. I've been reading through some of the documentation and books, and done some searching here in the forums, but can't seem to find a way to make this work.

      I am on Oracle 11.1.0.6.0 SE.

      I have a table like this:
      ID     Product          Month_A_Amt Month_B_Amt Month_C_Amt     Month_D_Amt
      
      123     ProductA     3          5          7          9
      123     ProductB     2          4          6          8
      123     ProductC     10          11          12          13
      456     ProductA     1          2          3          4
      456     ProductB     3          4          5          6
      We get this data each month - Month_A is always the most recent month, and it goes back, so for this set, Month_A is November 09, Month_B is October 09, Month_C is Sept 09, etc. I am OK with hard-coding this value each month, so for the purposes of this exercise, just assume that Month_A is November 09, Month_B is Oct 09, Month_C is Sept 09, and Month_D is Aug 09.

      And I basically need to "Pivot" this table, so the end result looks like this:
      ID     Month          Product_A_Amt     Product_B_Amt     Product_C_Amt
      
      123     Nov 09          3          2          10
      123     Oct 09          5          4          11
      123     Sep 09          7          6          12
      123     Aug 09          9          8          13
      456     Nov 09          1          3          null
      456     Oct 09          2          4          null
      456     Sep 09          3          5          null
      456     Aug 09          4          6          null
      Below is the SQL to create the base table with test data. Now that I've typed up this explanation, it seems even simpler than I had made it out to be... but I'm still stumped, so any help is greatly appreciated, thanks!!

      create table test_base_table (
           ID     number,
           Product     varchar2(20),
           Month_a_amt     number,
           Month_b_amt     number,
           Month_c_amt     number,
           Month_d_amt     number);

      insert into test_base_table values (123, 'ProductA', 3, 5, 7, 9);
      insert into test_base_table values (123, 'ProductB', 2, 4, 6, 8);
      insert into test_base_table values (123, 'ProductC', 10, 11, 12, 13);
      insert into test_base_table values (456, 'ProductA', 1, 2, 3, 4);
      insert into test_base_table values (456, 'ProductB', 3, 4, 5, 6);

      Edited by: TheBlakester on Feb 10, 2010 7:56 PM
        • 1. Re: Help with PIVOT query (or advice on best way to do this)
          Tubby
          You can use the
           tags around your code to preserve the formatting (makes it easier to read). 
          
          And, from what you've posted, i couldn't discern where the MONTH information is being derived, can you explain that in better detail?                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
          • 4. Re: Help with PIVOT query (or advice on best way to do this)
            489554
            Thanks for the tip, I edited my initial post with the proper
             tags, so it lines up better now.  I also added a better explanation of how to derive the months from the columns - basically, Month_A is Nov 09, Month_B is Oct 09, Month_C is Sept 09 and Month_D is Aug 09.
            
            I'll look at the link that was posted, see if that helps.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
            • 5. Re: Help with PIVOT query (or advice on best way to do this)
              Tubby
              Not the nicest looking code, but i believe it suits your needs.
              ME_XE?select
                2     t.id,
                3     d.col1,
                4     max
                5     (
                6        case
                7           when t.product = 'ProductA' and d.col1 = 1 then t.month_a_amt
                8           when t.product = 'ProductA' and d.col1 = 2 then t.month_b_amt
                9           when t.product = 'ProductA' and d.col1 = 3 then t.month_c_amt
               10           when t.product = 'ProductA' and d.col1 = 4 then t.month_d_amt
               11        end
               12     ) as product_a_amt,
               13     max
               14     (
               15        case
               16           when t.product = 'ProductB' and d.col1 = 1 then t.month_a_amt
               17           when t.product = 'ProductB' and d.col1 = 2 then t.month_b_amt
               18           when t.product = 'ProductB' and d.col1 = 3 then t.month_c_amt
               19           when t.product = 'ProductB' and d.col1 = 4 then t.month_d_amt
               20        end
               21     ) as product_b_amt,
               22     max
               23     (
               24        case
               25           when t.product = 'ProductC' and d.col1 = 1 then t.month_a_amt
               26           when t.product = 'ProductC' and d.col1 = 2 then t.month_b_amt
               27           when t.product = 'ProductC' and d.col1 = 3 then t.month_c_amt
               28           when t.product = 'ProductC' and d.col1 = 4 then t.month_d_amt
               29        end
               30     ) as product_c_amt
               31  from test_base_table t, (select level as col1 from dual connect by level <= 4) d
               32  group by t.id, d.col1
               33  order by t.id, d.col1;
              
                              ID               COL1      PRODUCT_A_AMT      PRODUCT_B_AMT      PRODUCT_C_AMT
              ------------------ ------------------ ------------------ ------------------ ------------------
                             123                  1                  3                  2                 10
                             123                  2                  5                  4                 11
                             123                  3                  7                  6                 12
                             123                  4                  9                  8                 13
                             456                  1                  1                  3
                             456                  2                  2                  4
                             456                  3                  3                  5
                             456                  4                  4                  6
              
              8 rows selected.
              
              Elapsed: 00:00:00.95
              ME_XE?
              You would need to replace the COL1 with your date (which you say you can 'hardcode'). An example of how to put that into the query would be....
              ME_XE?select
                2     t.id,
                3     add_months(trunc(sysdate, 'MM'), -1*(d.col1 -1) ),
                4     max
                5     (
                6        case
                7           when t.product = 'ProductA' and d.col1 = 1 then t.month_a_amt
                8           when t.product = 'ProductA' and d.col1 = 2 then t.month_b_amt
                9           when t.product = 'ProductA' and d.col1 = 3 then t.month_c_amt
               10           when t.product = 'ProductA' and d.col1 = 4 then t.month_d_amt
               11        end
               12     ) as product_a_amt,
               13     max
               14     (
               15        case
               16           when t.product = 'ProductB' and d.col1 = 1 then t.month_a_amt
               17           when t.product = 'ProductB' and d.col1 = 2 then t.month_b_amt
               18           when t.product = 'ProductB' and d.col1 = 3 then t.month_c_amt
               19           when t.product = 'ProductB' and d.col1 = 4 then t.month_d_amt
               20        end
               21     ) as product_b_amt,
               22     max
               23     (
               24        case
               25           when t.product = 'ProductC' and d.col1 = 1 then t.month_a_amt
               26           when t.product = 'ProductC' and d.col1 = 2 then t.month_b_amt
               27           when t.product = 'ProductC' and d.col1 = 3 then t.month_c_amt
               28           when t.product = 'ProductC' and d.col1 = 4 then t.month_d_amt
               29        end
               30     ) as product_c_amt
               31  from test_base_table t, (select level as col1 from dual connect by level <= 4) d
               32  group by t.id, d.col1
               33  order by t.id, d.col1;
              
                              ID ADD_MONTHS(TRUNC(SYSDATE,'      PRODUCT_A_AMT      PRODUCT_B_AMT      PRODUCT_C_AMT
              ------------------ -------------------------- ------------------ ------------------ ------------------
                             123 01-FEB-2010 12 00:00                        3                  2                 10
                             123 01-JAN-2010 12 00:00                        5                  4                 11
                             123 01-DEC-2009 12 00:00                        7                  6                 12
                             123 01-NOV-2009 12 00:00                        9                  8                 13
                             456 01-FEB-2010 12 00:00                        1                  3
                             456 01-JAN-2010 12 00:00                        2                  4
                             456 01-DEC-2009 12 00:00                        3                  5
                             456 01-NOV-2009 12 00:00                        4                  6
              
              8 rows selected.
              
              Elapsed: 00:00:01.23
              ME_XE?
              Where i have used SYSDATE as my known start date.

              I don't have an 11 install sitting around, so i can't test the PIVOT option, but you should be able to work that in to this (replacing the MAX(DECODE( code ) if you have an unknown number of Products....

              Edited by: Tubby on Feb 10, 2010 8:01 PM
              • 6. Re: Help with PIVOT query (or advice on best way to do this)
                751969
                please go through my last post on pivoting for simple solution or download 2.txt from [http://www.box.net/shared/qpe9bbj4nn]

                Cheers,

                Gitesh

                Edited by: user12570554 on Feb 10, 2010 8:09 PM
                • 8. Re: Help with PIVOT query (or advice on best way to do this)
                  Frank Kulash
                  Hi,

                  Thanks for posting the CREATE TABLE and INSERT statements; that's very helpful.

                  Here's a solution using the Oracle 11 UNPIVOT and PIVOT features:
                  SELECT       id
                  ,       TO_CHAR ( ADD_MONTHS ( TO_DATE ( 'Nov 2009'
                                                          , 'Mon YYYY'
                                            )
                                        , month_num
                                        )
                              , 'Mon YYYY'
                              )          AS month
                  ,       producta
                  ,       productb
                  ,       productc
                  FROM       test_base_table
                  UNPIVOT       ( amt FOR month_num IN ( month_a_amt     AS  0
                                                  , month_b_amt     AS -1
                                                  , month_c_amt     AS -3
                                                  , month_d_amt     AS -4
                                          )
                         )
                  PIVOT       ( SUM (amt) FOR product IN ( 'ProductA'  AS producta
                                                       , 'ProductB'  AS productb
                                           , 'ProductC'  AS productc
                                           )
                         )
                  ORDER BY  id
                  ,            month_num     DESC
                  ;
                  The most recent month (November 2009, in this example) is hard-coded in one place near the beginning. You can use a variable or a sub-query instead of hard-coding.
                  1 person found this helpful
                  • 9. Re: Help with PIVOT query (or advice on best way to do this)
                    489554
                    Frank,

                    THANK YOU!! EXCELLENT, this is precisely the kind of thing I was looking for!! To further challenge you, I actually have it setup where I have 2 different totals/amounts for each month that I need to track separately. So in reality, my table looks like this:
                    ID    Product    Month_A_Amt_1   Month_A_Amt_2   Month_B_Amt_1  Month_B_Amt_2  Month_C_Amt_1  Month_C_Amt_2  Month_D_Amt_1  Month_D_Amt_2
                    
                    123   ProductA       3               9                5                9            7               9          9              9
                    123   ProductB       2               9                4                9            6               9          8              9
                    123   ProductC       10              9                11               9            12              9          13             9
                    456   ProductA       1               9                2                9            3               9          4              9
                    456   ProductB       3               9                4                9            5               9          6              9
                    So I need to track these 2 separately, so the final table should look like this:
                    ID     Month     Product_A_Amt_1  Product_A_Amt_2  Product_B_Amt_1  Product_B_Amt_2  Product_C_Amt_1  Product_c_Amt_2
                    
                    123     Nov 09          3             9                  2                   9          10                 9
                    123     Oct 09          5             9                  4                   9          11                 9
                    123     Sep 09          7             9                  6                   9          12                 9
                    123     Aug 09          9             9                  8                   9          13                 9
                    456     Nov 09          1             9                  3                   9          null              null
                    456     Oct 09          2             9                  4                   9          null              null
                    456     Sep 09          3             9                  5                   9          null              null
                    456     Aug 09          4             9                  6                   9          null              null
                    Here is updated SQL code to create the table.

                    create table test_base_table (
                         ID     number,
                         Product     varchar2(20),
                         Month_a_amt_1     number,
                         Month_a_amt_2     number,
                         Month_b_amt_1     number,
                         Month_b_amt_2     number,
                         Month_c_amt_1     number,
                         Month_c_amt_2     number,
                         Month_d_amt_1     number,
                         Month_d_amt_2     number);

                    insert into test_base_table values (123, 'ProductA', 3, 9, 5, 9, 7, 9, 9, 9);
                    insert into test_base_table values (123, 'ProductB', 2, 9, 4, 9, 6, 9, 8, 9);
                    insert into test_base_table values (123, 'ProductC', 10, 9, 11, 9, 12, 9, 13, 9);
                    insert into test_base_table values (456, 'ProductA', 1, 9, 2, 9, 3, 9, 4, 9);
                    insert into test_base_table values (456, 'ProductB', 3, 9, 4, 9, 5, 9, 6, 9);


                    I tried taking your code and tweaking it to do this, but having issues. Does it have to have a whole separate PIVOT and UNPIVOT clause for the amt_2 values?? That is what I tried below, but getting errors about "column ambiguously defined". Again, help is GREATLY appreciated here!!
                    SELECT id,
                      TO_CHAR ( ADD_MONTHS ( TO_DATE ( 'Nov 2009' , 'Mon YYYY' ) , month_num ) , 'Mon YYYY' ) AS MONTH ,
                      producta_1,
                      producta_2,
                      productb_1,
                      productb_2,
                      productc_1,
                      productc_2
                       FROM test_base_table UNPIVOT 
                         ( amt_1 FOR month_num IN ( month_a_amt_1 AS 0 , month_b_amt_1 AS -1 , month_c_amt_1 AS -3 , month_d_amt_1 AS -4 ) ) 
                         PIVOT ( SUM (amt_1) FOR product IN ( 'ProductA' AS producta_1 , 'ProductB' AS productb_1 , 'ProductC' AS productc_1 ) )
                         UNPIVOT 
                         ( amt_2 FOR month_num IN ( month_a_amt_2 AS 0 , month_b_amt_2 AS -1 , month_c_amt_2 AS -3 , month_d_amt_2 AS -4 ) ) 
                         PIVOT ( SUM (amt_2) FOR product IN ( 'ProductA' AS producta_2 , 'ProductB' AS productb_2 , 'ProductC' AS productc_2 ) )
                    ORDER BY id ,
                      month_num DESC ;
                    • 10. Re: Help with PIVOT query (or advice on best way to do this)
                      Frank Kulash
                      Hi,

                      You don't want to do multiple UNPIVOT and PIVOT clauses; you want to do multiple sets of columns in the one UNPIVOT clause and the one PIVOT cluase.

                      In the UNPIVOT clause, it's jsut a matter of replacing the single column "amt" before the FOR keyword with a parenthesized list "(amt_1, amt_2)", and replacing each column in the IN list (e.g. "month_a_amt") with a list of the same length ("(month_a_amt_1, month_a_amt_2)").
                      In the PIVOT clause, it's just a matter of replacing the single aggregate function "SUM (amt)" with an unparenthesized list of functions, each with an alias ("SUM (amt_1) AS amt_1, SUM (amt_2) AS amt_2". The aliases will automaticallly be added to the end of the output column names givien in the IN clause.
                      SELECT       id
                      ,       TO_CHAR ( ADD_MONTHS ( TO_DATE ( 'Nov 2009'
                                                              , 'Mon YYYY'
                                                )
                                            , month_num
                                            )
                                  , 'Mon YYYY'
                                  )          AS month
                      ,       product_a_amt_1
                      ,       product_a_amt_2
                      ,       product_b_amt_1
                      ,       product_b_amt_2
                      ,       product_c_amt_1
                      ,       product_c_amt_2
                      FROM       test_base_table
                      UNPIVOT       ( (amt_1,         amt_2      )     FOR month_num IN ( 
                               (month_a_amt_1, month_a_amt_2)     AS  0,
                               (month_b_amt_1, month_b_amt_2)     AS -1,
                               (month_c_amt_1, month_c_amt_2)     AS -3,
                               (month_d_amt_1, month_d_amt_2)     AS -4              )
                             )
                      PIVOT       ( SUM (amt_1) AS amt_1
                             , SUM (amt_2) AS amt_2 FOR product IN ( 'ProductA' AS product_a
                                                                          , 'ProductB' AS product_b
                                                         , 'ProductC' AS product_c
                                                         )
                             )
                      ORDER BY  id
                      ,            month_num     DESC
                      ;
                      Output:
                      .              PRODUCT  PRODUCT  PRODUCT  PRODUCT  PRODUCT  PRODUCT
                        ID MONTH    _A_AMT_1 _A_AMT_2 _B_AMT_1 _B_AMT_2 _C_AMT_1 _C_AMT_2
                      ---- -------- -------- -------- -------- -------- -------- --------
                       123 Nov 2009        3        9        2        9       10        9
                       123 Oct 2009        5        9        4        9       11        9
                       123 Aug 2009        7        9        6        9       12        9
                       123 Jul 2009        9        9        8        9       13        9
                       456 Nov 2009        1        9        3        9
                       456 Oct 2009        2        9        4        9
                       456 Aug 2009        3        9        5        9
                       456 Jul 2009        4        9        6        9
                      It looks like all the new amt2 values are 9. Do you think that's the best test? I think different numbers, like you used for the earlier sample data, would reduce the chances of getting the right results purely by coincidence.

                      If you want to experiment with queries like this, I suggest you use "SELECT *" (nothing else added) as the SELECT clause. Start with just an UNPIVOT operation. Some examples in the documentation do a CREATE TABLE AS ... to save the results of an UNPIVOT, and use that table as the base table for a PIVOT. I think that's a neat idea for reducing confusion.
                      • 11. Re: Help with PIVOT query (or advice on best way to do this)
                        489554
                        Frank, you are THE MAN, thank you!!! I really appreciate you taking the time to look into this, and provide me the example. Not only the code, but the nice explanation, so I can figure out how it is all working!! I was new to these PIVOT and UNPIVOT functions, I think I had a solid understanding of using them each separately, but it gets confusing when you slap the 2 together!!

                        Thanks again!