Oracle Dynamic SQL for columns

Question

I am writing a stored procedure for which I need to populate a table based on the data being reported on.

In this situation, I will be pulling in three values per day for a certain code in a date range.

Say on a certain run of this stored procedure, I have code values X, Y, and Z for a date range as so:

select abc.code, 
       abc.date, 
       abc.val_1, 
       abc.val_2, 
       abc.val_3
  from data.abc
 where abc.date BETWEEN '01-OCT-2009' AND '31-OCT-2009'

So for each day in the date range, I have three records for codes x,y, and z.

In my final table, I need to transform this from rows to columns. Normally I would use the decode function, but here I want to create my final table dynamically based on the data coming back.

In this case I would have one record for each day in the range and have 9 more columns (val_1_X, val_2_x, val_3_x, val_1_y, and so on).

I would like to set this up dynamically so that I do not need to re-open my stored procedure when a new "code" is introduced and so that on each instance of the report, only the "codes" being returned on that instance of the report are included on the final table.

Is this possible through dynamic sql? I am on Oracle version 10g.

Answer 1

From my understanding, 01-OCT-2009 to 31-OCT-2009 has only codes x, y and z, while going through to 30-NOV-2009 might also yield you w.

Ultimately, you want a query like this:

CREATE TABLE t1 AS
  SELECT
    abc.date,
    MAX(DECODE(code, 'x', val_1, NULL)) AS abc_val_1_x,
    MAX(DECODE(code, 'x', val_2, NULL)) AS abc_val_2_x,
    MAX(DECODE(code, 'x', val_3, NULL)) AS abc_val_3_x,
    MAX(DECODE(code, 'y', val_1, NULL)) AS abc_val_1_y,
    MAX(DECODE(code, 'y', val_2, NULL)) AS abc_val_2_y,
    MAX(DECODE(code, 'y', val_3, NULL)) AS abc_val_3_y,
    ...
  FROM data.abc
  WHERE abc.date BETWEEN '01-OCT-2009' AND '31-OCT-2009'
  GROUP BY abc.date

I'm not an Oracle guy and don't have access to test on Oracle, so you might find quite a few syntax errors and the like below. You'll also need to change the hard coded dates to variables, declare some variables and actually run the created query.

-- Figure out all valid codes for date range.
-- Might also require ordering depending on your report.
CURSOR c1
  IS
    SELECT
      abc.code
    FROM data.abc
    WHERE abc.date BETWEEN '01-OCT-2009' AND '31-OCT-2009'
    GROUP BY abc.code;

query1 := 'CREATE TABLE abc_report AS SELECT date';
LOOP
    FETCH c1 INTO code1
    EXIT WHEN c1%NOTFOUND;
    query1 := query1 || ', DECODE(code, ''' || code1 || ''', val_1, NULL)) AS abc_val_1_' || code1 ||
        ', DECODE(code, ''' || code1 || ''', val_2, NULL)) AS abc_val_2_' || code1 ||
        ', DECODE(code, ''' || code1 || ''', val_3, NULL)) AS abc_val_3_' || code1
END LOOP;
query1 := query1 || ' FROM data.abc WHERE date BETWEEN ''01-OCT-2009'' AND ''31-OCT-2009'''

Answer 2

Thanks so much for the help, but it turns out I'm not going to be able to go down this path after all. The stored procdure was to be kicked off and used by a reporting tool, and I need to be prepared for possiblity that multiple instances of this report will be running simultaneously, so I can't be re-using a table name and I won't be allowed to have a stored procedure that creates a table unique to each run instance.

Thanks for the help again though!