This discussion is archived
11 Replies Latest reply: Feb 11, 2010 2:54 PM by 489554 RSS

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

489554 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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?                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 2. Re: Help with PIVOT query (or advice on best way to do this)
    Aketi Jyuuzou Oracle ACE
    Currently Being Moderated
    There is very similar thread
    Row to cols
  • 4. Re: Help with PIVOT query (or advice on best way to do this)
    489554 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.
  • 9. Re: Help with PIVOT query (or advice on best way to do this)
    489554 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points