Forum Stats

  • 3,854,952 Users
  • 2,264,439 Discussions
  • 7,905,846 Comments

Discussions

MODEL/PIVOT : Help pls

946141
946141 Member Posts: 15
edited Jan 23, 2014 4:48AM in SQL & PL/SQL


Hi,

I am trying to convert rows to columns using Model/Pivot in Oracle 11g. Please find the data below.

CREATE TABLE TEST_DATA_FIN
(NUM NUMBER(9),
ID  NUMBER(9),
YEAR NUMBER(4),
VALUE NUMBER(9,2))


INSERT INTO TEST_DATA_FIN
VALUES(1,100,2014, 1) ;

INSERT INTO TEST_DATA_FIN
VALUES(1,100,2014, 2) ;

INSERT INTO TEST_DATA_FIN
VALUES(1,100,2014, 3);
INSERT INTO TEST_DATA_FIN
VALUES(1,200,2014, 4);

INSERT INTO TEST_DATA_FIN
VALUES(1,200,2014, 5);

INSERT INTO TEST_DATA_FIN
VALUES(1,200,2014, 6);

INSERT INTO TEST_DATA_FIN
VALUES(1,100,2013, 7) ;

INSERT INTO TEST_DATA_FIN
VALUES(1,100,2013, 8) ;

INSERT INTO TEST_DATA_FIN
VALUES(1,100,2013, 9);

  INSERT INTO TEST_DATA_FIN
VALUES(1,200,2013, 10) ;

INSERT INTO TEST_DATA_FIN
VALUES(1,200,2013, 11) ;

INSERT INTO TEST_DATA_FIN
VALUES(1,200,2013, 12);

INSERT INTO TEST_DATA_FIN
VALUES(2,100,2013, 13) ;

INSERT INTO TEST_DATA_FIN
VALUES(2,100,2013, 14) ;

INSERT INTO TEST_DATA_FIN
VALUES(2,100,2013, 15);

INSERT INTO TEST_DATA_FIN
VALUES(2,200,2013, 16);

INSERT INTO TEST_DATA_FIN
VALUES(2,200,2013, 17);

INSERT INTO TEST_DATA_FIN
VALUES(2,200,2013, 18);

INSERT INTO TEST_DATA_FIN
VALUES(2,100,2012, 19) ;

INSERT INTO TEST_DATA_FIN
VALUES(2,100,2012, 20) ;

INSERT INTO TEST_DATA_FIN
VALUES(2,100,2012, 21);

  INSERT INTO TEST_DATA_FIN
VALUES(2,200,2012, 22) ;

INSERT INTO TEST_DATA_FIN
VALUES(2,200,2012, 23) ;

INSERT INTO TEST_DATA_FIN
VALUES(2,200,2012, 24);

For Ex ,
For NUM = 1
IF MAX(YEAR) = 2014
then a query that returns
new columns
            NUM,
            ID,
            YEAR
            YEAR1_DATA with 1,
              YEAR2_DATA with 2,
              YEAR3_data with 3 and so on..

For ex:
FOR NUM = 2
IF MAX(YEAR)  = 2013
then the  query  should return
new columns
            NUM                      = 2,
            ID,
              YEAR1_DATA with 13,
              YEAR2_DATA with 14,
              YEAR3_data    with 15  and so on..

The final output  should be in the following format. Was able to implement it with Decode/Group by.  I am trying to write a query using MODEL/PIVOT.
Can you pls help me.


NUM    ID    YEAR      YEAR1_DATA    YEAR2_DATA    YEAR3_DATA

--------------------------------------------------------------------------------------------------
1        100    2014                  1                    2                           3
1        200    2014                  4                    5                           6
1        100    2013                  7                    8                           9

2        200    2013                  10                  11                         12
2        100    2013                  13                  14                        15
2        200    2013                  16                  17                        18
2        100    2012                   19                  20                       21
2        200    2012                   22                  23                       24

Thank you so much. I really appreciate any help.


Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,506 Red Diamond
    edited Jan 22, 2014 1:51PM Answer ✓

    Hi,

    946141 wrote:
    
    Thank you so much for your answers. I messed up the data little bit. Very sorry about that.
    With the following data,
    MAX(YEAR) value should go in YEAR1_DATA and MAX(YEAR) - 1 value shoudl go in YEAR2_DATA.
    I am so sorry again and really appreciate your help.
    The output should be as follows
    NUM       ID        YEAR1_DATA    YEAR2_DATA    YEAR3_DATA
    ---------------------------------------------------------------------------------------
    1         100           33333             22222     11111
    1         200          44444      66666     55555       
    2         100          333333     222222    111111
    2         200          4444444    666666         555555
    
    INSERT INTO TEST_DATA_FIN
    VALUES(1,100,2014, 33333) ;
    
    INSERT INTO TEST_DATA_FIN
    VALUES(1,100,2013, 22222) ;
    
    INSERT INTO TEST_DATA_FIN VALUES(1,100,2012, 11111);

    INSERT INTO TEST_DATA_FIN VALUES(1,200,2014, 44444);
    INSERT INTO TEST_DATA_FIN VALUES(1,200,2013, 66666);
    INSERT INTO TEST_DATA_FIN VALUES(1,200,2012, 55555); ----------------- INSERT INTO TEST_DATA_FIN VALUES(2,100,2013, 333333) ; INSERT INTO TEST_DATA_FIN VALUES(2,100,2012, 222222) ;
    INSERT INTO TEST_DATA_FIN VALUES(2,100,2011, 111111);

    INSERT INTO TEST_DATA_FIN VALUES(1,200,2013, 4444444);
    INSERT INTO TEST_DATA_FIN VALUES(1,200,2012, 666666);
    INSERT INTO TEST_DATA_FIN VALUES(1,200,2011, 555555);

    I think you want something like:

    WITH  got_r_num  AS
    (
        SELECT  num, id, value
        ,       MAX (year) OVER (PARTITION BY  num, id)
              - year                 AS r_num
        FROM    test_data_fin
    )
    SELECT    *
    FROM      got_r_num
    PIVOT     (  MIN (value)
              FOR  r_num  IN  ( 0   AS year1_data
                              , 1   AS year2_data
                              , 2   AS year3_data
                              )
              )
    ORDER BY  num
    ,         id
    ;
     

    I don't see how you get the output you posted form that sample data.  Are there typos in one or the other?

    For example, in the sample data, 100 is the only id related to num=2, so I don't understand the output row that has num=2 and id=200. I'm guessing there's a mistake somewhere.

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,506 Red Diamond
    edited Jan 22, 2014 12:59PM

    Hi,

    Here's one way to use PIVOT:

    WITH  got_r_num  AS
    (
        SELECT  num, id, year, value
        ,       ROW_NUMBER () OVER ( PARTITION BY  num, id, year
                                     ORDER BY      value
                                   )   AS r_num
        FROM    test_data_fin
    )
    SELECT    *
    FROM      got_r_num
    PIVOT     (    MIN (value)
              FOR  r_num  IN  ( 1   AS year1_data
                              , 2   AS year2_data
                              , 3   AS year3_data
                              )
              )
    ORDER BY  num
    ,         year   DESC
    ,         id
    ;
     

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

    It would help[ if you posted your existing query (using DECODE and GROUP BY), too.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,729 Red Diamond

    This is dynamic pivot. You can make it static pivot if you know max number of years per NUM, ID combination. Assuming it is 3:

    with t as (

               select  num,

                       id,

                       year,

                       value,

                       row_number() over(partition by num,id,year order by value) rn

                 from  test_data_fin

              )

    select  num,

            id,

            year,

            sum(

                case rn

                  when 1 then value

                end

               ) year1_data,

            sum(

                case rn

                  when 2 then value

                end

               ) year2_data,

            sum(

                case rn

                  when 3 then value

                end

               ) year3_data

      from  t

      group by num,

               id,

               year

      order by num,

               year desc,

               id

    /


           NUM         ID       YEAR YEAR1_DATA YEAR2_DATA YEAR3_DATA
    ---------- ---------- ---------- ---------- ---------- ----------
             1        100       2014          1          2          3
             1        200       2014          4          5          6
             1        100       2013          7          8          9
             1        200       2013         10         11         12
             2        100       2013         13         14         15
             2        200       2013         16         17         18
             2        100       2012         19         20         21
             2        200       2012         22         23         24

    8 rows selected.

    [email protected] >

    SY.

  • 946141
    946141 Member Posts: 15

    Thank you so much for your answers. I messed up the data little bit. Very sorry about that.

    With the following data,

    MAX(YEAR) value should go in YEAR1_DATA and MAX(YEAR) - 1 value shoudl go in YEAR2_DATA.

    I am so sorry again and really appreciate your help.

    The output should be as follows

    NUM       ID        YEAR1_DATA    YEAR2_DATA    YEAR3_DATA 

    ---------------------------------------------------------------------------------------

    1         100           33333             22222     11111

    1         200          44444      66666     55555        

    2         100          333333     222222    111111

    2         200          4444444    666666         555555

    INSERT INTO TEST_DATA_FIN

    VALUES(1,100,2014, 33333) ;

    INSERT INTO TEST_DATA_FIN

    VALUES(1,100,2013, 22222) ;


    INSERT INTO TEST_DATA_FIN

    VALUES(1,100,2012, 11111);



    INSERT INTO TEST_DATA_FIN

    VALUES(1,200,2014, 44444);


    INSERT INTO TEST_DATA_FIN

    VALUES(1,200,2013, 66666);


    INSERT INTO TEST_DATA_FIN

    VALUES(1,200,2012, 55555);

    -----------------

    INSERT INTO TEST_DATA_FIN

    VALUES(2,100,2013, 333333) ;

    INSERT INTO TEST_DATA_FIN

    VALUES(2,100,2012, 222222) ;


    INSERT INTO TEST_DATA_FIN

    VALUES(2,100,2011, 111111);



    INSERT INTO TEST_DATA_FIN

    VALUES(1,200,2013, 4444444);


    INSERT INTO TEST_DATA_FIN

    VALUES(1,200,2012, 666666);


    INSERT INTO TEST_DATA_FIN

    VALUES(1,200,2011, 555555);

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,506 Red Diamond
    edited Jan 22, 2014 1:51PM Answer ✓

    Hi,

    946141 wrote:
    
    Thank you so much for your answers. I messed up the data little bit. Very sorry about that.
    With the following data,
    MAX(YEAR) value should go in YEAR1_DATA and MAX(YEAR) - 1 value shoudl go in YEAR2_DATA.
    I am so sorry again and really appreciate your help.
    The output should be as follows
    NUM       ID        YEAR1_DATA    YEAR2_DATA    YEAR3_DATA
    ---------------------------------------------------------------------------------------
    1         100           33333             22222     11111
    1         200          44444      66666     55555       
    2         100          333333     222222    111111
    2         200          4444444    666666         555555
    
    INSERT INTO TEST_DATA_FIN
    VALUES(1,100,2014, 33333) ;
    
    INSERT INTO TEST_DATA_FIN
    VALUES(1,100,2013, 22222) ;
    
    INSERT INTO TEST_DATA_FIN VALUES(1,100,2012, 11111);

    INSERT INTO TEST_DATA_FIN VALUES(1,200,2014, 44444);
    INSERT INTO TEST_DATA_FIN VALUES(1,200,2013, 66666);
    INSERT INTO TEST_DATA_FIN VALUES(1,200,2012, 55555); ----------------- INSERT INTO TEST_DATA_FIN VALUES(2,100,2013, 333333) ; INSERT INTO TEST_DATA_FIN VALUES(2,100,2012, 222222) ;
    INSERT INTO TEST_DATA_FIN VALUES(2,100,2011, 111111);

    INSERT INTO TEST_DATA_FIN VALUES(1,200,2013, 4444444);
    INSERT INTO TEST_DATA_FIN VALUES(1,200,2012, 666666);
    INSERT INTO TEST_DATA_FIN VALUES(1,200,2011, 555555);

    I think you want something like:

    WITH  got_r_num  AS
    (
        SELECT  num, id, value
        ,       MAX (year) OVER (PARTITION BY  num, id)
              - year                 AS r_num
        FROM    test_data_fin
    )
    SELECT    *
    FROM      got_r_num
    PIVOT     (  MIN (value)
              FOR  r_num  IN  ( 0   AS year1_data
                              , 1   AS year2_data
                              , 2   AS year3_data
                              )
              )
    ORDER BY  num
    ,         id
    ;
     

    I don't see how you get the output you posted form that sample data.  Are there typos in one or the other?

    For example, in the sample data, 100 is the only id related to num=2, so I don't understand the output row that has num=2 and id=200. I'm guessing there's a mistake somewhere.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,729 Red Diamond
    946141 wrote:
    
    The output should be as follows
    
    
     

    Not much of a difference:

    with t as (

               select  num,

                       id,

                       year,

                       value,

                       dense_rank() over(partition by num,id order by year desc) rn

                 from  test_data_fin

              )

    select  num,

            id,

            sum(

                case rn

                  when 1 then value

                end

               ) year1_data,

            sum(

                case rn

                  when 2 then value

                end

               ) year2_data,

            sum(

                case rn

                  when 3 then value

                end

               ) year3_data

      from  t

      group by num,

               id

      order by num,

               id

    /


           NUM         ID YEAR1_DATA YEAR2_DATA YEAR3_DATA
    ---------- ---------- ---------- ---------- ----------
             1        100      33333      22222      11111
             1        200      44444    4511110     722221
             2        100     333333     222222     111111

    [email protected] >

    SY.

    Solomon Yakobson
  • 946141
    946141 Member Posts: 15

    Oops. May be my brain has frozen today with this weather. Thank you so much.

    You gave the right query even though I didn't give the right data. It works perfectly.

    These are the insert statements .

    INSERT INTO TEST_DATA_FIN
    VALUES(1,100,2014, 33333) ;

    INSERT INTO TEST_DATA_FIN
    VALUES(1,100,2013, 22222) ;

    INSERT INTO TEST_DATA_FIN
    VALUES(1,100,2012, 11111);

    INSERT INTO TEST_DATA_FIN
    VALUES(1,200,2014, 44444);


    INSERT INTO TEST_DATA_FIN
    VALUES(1,200,2013, 66666);


    INSERT INTO TEST_DATA_FIN
    VALUES(1,200,2012, 55555);


    -----------------
    INSERT INTO TEST_DATA_FIN
    VALUES(2,100,2013, 333333) ;

    INSERT INTO TEST_DATA_FIN
    VALUES(2,100,2012, 222222) ;

    INSERT INTO TEST_DATA_FIN
    VALUES(2,100,2011, 111111);

    INSERT INTO TEST_DATA_FIN
    VALUES(2,200,2013, 4444444);


    INSERT INTO TEST_DATA_FIN
    VALUES(2,200,2012, 666666);


    INSERT INTO TEST_DATA_FIN
    VALUES(2,200,2011, 555555);

    Just for curiosity, is there anyway we can do this with Model?

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,506 Red Diamond
    edited Jan 22, 2014 2:42PM

    Hi,

    946141 wrote:
    
    ... Just for curiosity, is there anyway we can do this with Model?
     

    I don't see how MODEL would help, but maybe somebody who's more familiar with MODEL has a different idea.

    MODEL is really useful when the output you need isn't actually in the table, for example, if you need to extrapolate values for missing years.  In this problem, all the values we want to display are already in the table; we just need to arrange how they are displayed.

  • Partha Sarathy S
    Partha Sarathy S Member Posts: 1,200 Silver Trophy

    Here is one way of doing it using MODEL clause.

    SELECT NUM,ID,year1_data,year2_data,year3_data FROM (
    SELECT NUM,ID,YEAR,year1_data,year2_data,year3_data,row_number()OVER(PARTITION BY NUM,ID ORDER BY 'A') RN
    FROM (
    SELECT NUM,ID,YEAR,VAL,year1_data,year2_data,year3_data
    FROM (SELECT NUM,ID,YEAR,VALUE val,
          ROW_NUMBER()OVER(PARTITION BY NUM,ID ORDER BY YEAR DESC) RNK FROM TEST_DATA_FIN)
    MODEL RETURN UPDATED ROWS
      DIMENSION BY (num,id,rnk)
      MEASURES (val,YEAR, 0 year1_data, 0 year2_data, 0 year3_data)
      RULES(year1_data[num,id,rnk]
            = val[cv(),cv(),1],
            year2_data[num,id,rnk]
            = val[cv(),cv(),2],
            year3_data[num,id,rnk]
            = val[cv(),cv(),3])
    )
    )
    WHERE RN=1;

    OUTPUT:        NUM         ID YEAR1_DATA YEAR2_DATA YEAR3_DATA ---------- ---------- ---------- ---------- ----------          1        100      33333      22222      11111          1        200      44444      66666      55555          2        100     333333     222222     111111          2        200    4444444     666666     555555
This discussion has been closed.