1

I currently have a static pivot query that is working.

WITH    ROW_SET    AS
(
SELECT DEFKEY.CF_DEFERRAL_DURATION, KEYACM.CF_KEYWORD_ID, KEYACM.CF_PERIOD, KEYACM.CF_VALUE,
row_number () OVER ( PARTITION BY  KEYACM.CF_KEYWORD_ID
                      ORDER BY      KEYACM.CF_PERIOD desc
           )   AS r_num
           FROM AMEXIV.MAS_CFUS_KEYACM_CONTROLDATA KEYACM
           JOIN AMEXIV.MAS_CFUS_DEFKEY_CONTROLDATA DEFKEY
           ON DEFKEY.CF_MODEL_ID = KEYACM.CF_MODEL_ID
           AND DEFKEY.CF_KEYWORD_ID = CONCAT(KEYACM.CF_KEYWORD_ID, '_accum_deferral')
)
SELECT    *
FROM      ROW_SET
PIVOT     (   MIN (ROW_SET.CF_VALUE)     AS VALUE
          ,   MIN (ROW_SET.CF_PERIOD)  AS PERIOD
          FOR r_num  IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
          ) KEYACM

I'd like to convert the line FOR r_num IN (1, 2, 3...etc) so that it instead uses the value from the field DEFKEY.CF_DEFERRAL_DURATION. That field is the number of months that the calculation should happen and it currently will do the calculation correctly only for records that have a Deferral Duration of 12 months. But there are others that have 6 months.

Is there a way to read this value from the DEFKEY.CF_DEFERRAL_DURATION field?

Michael Novo
  • 7
  • 1
  • 1
    Dynamic Pivot = Dynmaic SQL, you have to build the SQL as a string substitute in the variable you need for your in and then execute the sql string. http://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql may work depending... http://stackoverflow.com/questions/14156928/oracle-dynamic-column-name if columns are dynamic too. – xQbert Dec 24 '14 at 16:50

0 Answers0