1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Using pivot table to retrieve static two years of data

Discussion in 'SQL PL/SQL' started by Michael Novo, Aug 26, 2014.

  1. Michael Novo

    Michael Novo Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Greetings! First time question!

    I have a table MAS_CFUS_KEYACM
    Which contains fields: CF_KEYWORD_ID, CF_VALUE, CF_PERIOD

    The table normally returns values such as:

    CF_KEYWORD_ID CF_VALUE CF_PERIOD
    -------------------------------------------
    A 320.50 01-DEC-12
    A 650.50 01-JAN-13
    A 220.50 01-FEB-13
    A 100.50 01-JAN-14
    A 300.25 01-FEB-14
    A 500.74 01-MAR-14
    B 450.50 01-DEC-14
    B 750.50 01-JAN-14
    B 200.15 01-FEB-14
    B 600.34 01-MAR-14

    CF_PERIOD is always a unique date for each CF_KEYWORD_ID and can go back several years, but never contains future data, only up to the current period in time.

    What I would like to see as a final result is grouped by the CF_KEYWORD_ID, all of the CF_VALUE and CF_PERIOD fields in columns such as a PIVOT TABLE but only for two years including the current year and previous year even if there are no future dates.

    CF_KEYWORD_ID JAN-14 FEB-14 MAR-14 (continues through DEC-14) JAN-13 FEB-13 (continues through DEC-13)
    -------------------------------------------------------------------------------------------------------------------------------------------------------------
    A 100.50 300.25 500.74 650.50 220.50
    B 750.50 200.15 600.34

    **Note that the 2012 value // 01-DEC-12 should be excluded. So the query always looks at the parameter date that has been input and always gives back the current year (all the way to DEC even if there is no data) and all of the previous year even if some of the months have no data. The fields in essence should be static.

    Can anyone help? Currently I have the below pivot query which does work to return 24 months of data, however it's dynamic...I always just want the full current year and the full previous year (nulls are ok):
    WITH KEYACM AS
    (
    SELECT CF_PERIOD, CF_VALUE, CF_KEYWORD_ID,
    row_number () OVER ( PARTITION BY CF_KEYWORD_ID
    ORDER BY CF_PERIOD desc
    ) AS r_num
    FROM MAS_CFUS_KEYACM
    WHERE TO_CHAR(CF_PERIOD, 'YYYY') >= '2014' - 1
    )
    SELECT *
    FROM KEYACM
    PIVOT ( MIN (KEYACM.CF_VALUE) AS VALUE
    , MIN (KEYACM.CF_PERIOD) AS PERIOD
    FOR r_num IN (24, 23, 22, 21, 20, 19, 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1)
     

    Attached Files:

  2. Michael Novo

    Michael Novo Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Looks like I'm getting a bit closer. This gives me the exact layout I'm looking for, but just need it to be a static day and month and take the year off of the parameter. Any tips on what to write for the "IN" statement with all the dates below? I can't have the year hard coded.

    WITH KEYACM AS
    (
    SELECT CF_PERIOD, CF_VALUE, CF_KEYWORD_ID
    FROM AMEXIV.MAS_CFUS_KEYACM_CONTROLDATA
    )
    SELECT *
    FROM KEYACM
    PIVOT ( MIN (KEYACM.CF_VALUE) AS VALUE
    , MIN (KEYACM.CF_PERIOD) AS PERIOD
    FOR CF_PERIOD IN ('01-JAN-14', '01-FEB-14', '01-MAR-14', '01-APR-14', '01-MAY-14', '01-JUN-14', '01-JUL-14', '01-AUG-14', '01-SEP-14', '01-OCT-14', '01-NOV-14', '01-DEC-14',
    '01-JAN-13', '01-FEB-13', '01-MAR-13', '01-APR-13', '01-MAY-13', '01-JUN-13', '01-JUL-13', '01-AUG-13', '01-SEP-13', '01-OCT-13', '01-NOV-13', '01-DEC-13')
    )
    ORDER BY CF_KEYWORD_ID
    ;
     
  3. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Without your table, I can't test anything. I don't use PIVOT much, but testing indicates that the 'FOR xxx IN' list has to be static. The only way I can see (without involving PL/SQL) to make it static and allow you to return data from more than one year that I could think of was to make the list show only month values. The following example grabs two years worth of hire-dates from HR.EMPLOYEES and UNIONs two PIVOTs together.

    I don't know if this could be modified into something you could use to get your data.

    Code (Text):
    SELECT * from (
       select '2006 data' AS break_col, TO_CHAR(TRUNC(hire_date, 'MM'), 'DD-MON') MONTH_HIRED
       from   hr.employees
       where  TO_CHAR(hire_date, 'YYYY') = '2006'
    )
    pivot
    (
       count(month_hired) emp_count
       for month_hired in ('01-JAN','01-FEB', '01-MAR')
    )
    UNION
    SELECT * from (
       select '2007 data' AS break_col, TO_CHAR(TRUNC(hire_date, 'MM'), 'DD-MON') MONTH_HIRED
       from   hr.employees
       where  TO_CHAR(hire_date, 'YYYY') = '2007'
    )
    pivot
    (
       count(month_hired) emp_count
       for month_hired in ('01-JAN','01-FEB', '01-MAR')
    )


    BREAK_COL '01-JAN'_EMP_COUNT '01-FEB'_EMP_COUNT '01-MAR'_EMP_COUNT
    --------- ------------------ ------------------ ------------------
    2006 data                  3                  3                  5
    2007 data                  1                  3                  3
     

    You can dynamically get the start of the current year via TRUNC(SYSDATE, 'YYYY') and the previous year via TRUNC(ADD_MONTHS(SYSDATE, -12), 'YYYY'), so your WITH clauses would look something like:

    Code (Text):
    WITH KEYACM AS
    (
    SELECT CF_PERIOD, CF_VALUE, CF_KEYWORD_ID
    FROM   AMEXIV.MAS_CFUS_KEYACM_CONTROLDATA
    WHERE  TRUNC(CF_PERIOD, 'YYYY') = TRUNC(SYSDATE, 'YYYY')

    and

    WITH KEYACM AS
    (
    SELECT CF_PERIOD, CF_VALUE, CF_KEYWORD_ID
    FROM   AMEXIV.MAS_CFUS_KEYACM_CONTROLDATA
    WHERE  TRUNC(CF_PERIOD, 'YYYY') = TRUNC(ADD_MONTHS(SYSDATE, -12), 'YYYY')
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This may help:


    Code (SQL):

    SQL> CREATE TABLE name_qty(
      2        name      varchar2(20),
      3        quantity      NUMBER
      4  );


    TABLE created.


    SQL>
    SQL> INSERT ALL
      2  INTO name_qty
      3  VALUES('COL_A', 5000)
      4  INTO name_qty
      5  VALUES('COL_B', 3000)
      6  INTO name_qty
      7  VALUES('COL_C', 3000)
      8  SELECT * FROM dual;


    3 ROWS created.


    SQL>
    SQL> commit;


    Commit complete.


    SQL>
    SQL> SELECT name, quantity
      2  FROM name_qty;


    NAME                   QUANTITY                                                
    -------------------- ----------                                                
    COL_A                      5000                                                
    COL_B                      3000                                                
    COL_C                      3000                                                


    SQL>
    SQL> COLUMN namelist new_value n_list noprint
    SQL>
    SQL> SELECT listagg(''''||name||'''',',') WITHIN GROUP (ORDER BY name) namelist
      2  FROM name_qty
      3  CONNECT BY nocycle name = prior name
      4  GROUP BY level;
                                                                                   
                                                                                   
    SQL>
    SQL> SELECT *
      2  FROM
      3  (SELECT name, quantity
      4   FROM name_qty nq
      5  )
      6  pivot
      7  (
      8        SUM(quantity)
      9        FOR name IN (&n_list)
     10  );


       'COL_A'    'COL_B'    'COL_C'                                                
    ---------- ---------- ----------                                                
          5000       3000       3000                                                


    SQL>
    SQL> INSERT ALL
      2  INTO name_qty
      3  VALUES('COL_D', 5000)
      4  INTO name_qty
      5  VALUES('COL_E', 3000)
      6  INTO name_qty
      7  VALUES('COL_F', 3000)
      8  SELECT * FROM dual;


    3 ROWS created.


    SQL>
    SQL> commit;


    Commit complete.


    SQL>
    SQL> SELECT listagg(''''||name||'''',',') WITHIN GROUP (ORDER BY name) namelist
      2  FROM name_qty
      3  CONNECT BY nocycle name = prior name
      4  GROUP BY level;
                                                                                   
                                                                                   
    SQL>
    SQL> SELECT *
      2  FROM
      3  (SELECT name, quantity
      4   FROM name_qty nq
      5  )
      6  pivot
      7  (
      8        SUM(quantity)
      9        FOR name IN (&n_list)
     10  );


       'COL_A'    'COL_B'    'COL_C'    'COL_D'    'COL_E'    'COL_F'              
    ---------- ---------- ---------- ---------- ---------- ----------              
          5000       3000       3000       5000       3000       3000              


    SQL>
     

    Notice that a variable was populated with a query and used for the IN list.
     
  5. Michael Novo

    Michael Novo Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Thanks David, but I'm unable to run "CREATE TABLE" in my permissions. Matthew, I'm working on the WITH clause you posted, but it's not working exactly as expected for me yet.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That is not the issue, what I posted boils down to the use of a 'dynamic' way to generate your column list. Look at the queries, not the DDL.