Oracle Pivot table

[Jeremy Wilson]

I have a problem with an application that I am attempting to create of a legacy table of data. The table format is

datetime(timestamp) value_id(int) value(float).

I need to transform this into the following resultset filtered by a set of at most 9 value_id.

datetime, value_id_1,value_id_1,...,value_id_9

the row values will be datetime with the corresponding float value for the id which is not the column name. I believe I need to use a pivot table but am unfamiliar with this type of solution. If anyone could provide sample code or point me in the right direction that would be great. I can not modify the data structure but can accomplish this with the use of stored procedures on Oracle 9i Standard edition

Thanks in advance...

Jeremy

[Julian Kennedy]

Hi Jeremy,

From a quick Google, Oracle 9i pivot table syntax is as follows:

SELECT * FROM
TABLE(pivot_package.pivot(100));

Generates 100 rows numbered 1 to 100.

I'm not sure how this helps you with your problem, however.
I've solved that kind of problem before. If you feel you need more help I'll try and remember how.

Jules

[Jeremy Wilson]

What is pivot_package a part of. Do not believe it is part of Oracle Standard edition

[Julian Kennedy]

Dunno. Try searching Oracle's web site.

No matter. You can achieve the same effect with the following code:

CREATE TABLE pivot (
   x NUMBER
   );
 
INSERT INTO pivot
SELECT tens.x + ones.x
FROM
  (SELECT 0 x FROM dual
   UNION SELECT 1 FROM dual
   UNION SELECT 2 FROM dual
   UNION SELECT 3 FROM dual
   UNION SELECT 4 FROM dual
   UNION SELECT 5 FROM dual
   UNION SELECT 6 FROM dual
   UNION SELECT 7 FROM dual
   UNION SELECT 8 FROM dual
   UNION SELECT 9 FROM dual) ones,
  (SELECT 0 x FROM dual
   UNION SELECT 10 FROM dual
   UNION SELECT 20 FROM dual
 
   UNION SELECT 30 FROM dual
   UNION SELECT 40 FROM dual
   UNION SELECT 50 FROM dual
   UNION SELECT 60 FROM dual
   UNION SELECT 70 FROM dual
   UNION SELECT 80 FROM dual
   UNION SELECT 90 FROM dual) tens;

I'd be interested to know how this helps solve the problem.

Jules

[Julian Kennedy]

Hmmm, that's what happens when you skim an article. Here is the code for pivot_package and its dependencies from oracle.com:

CREATE OR REPLACE TYPE pivot_row AS OBJECT (
   x NUMBER
);
/
 
CREATE OR REPLACE TYPE pivot_table
   AS TABLE OF pivot_row;
/
 

CREATE OR REPLACE PACKAGE pivot_package AS
FUNCTION pivot (num_rows IN NUMBER)
   RETURN pivot_table
   PARALLEL_ENABLE PIPELINED;
END;
/
 

CREATE OR REPLACE PACKAGE BODY pivot_package AS
   FUNCTION pivot (num_rows IN NUMBER)
   RETURN pivot_table PARALLEL_ENABLE PIPELINED IS
         outrow pivot_row := pivot_row (0);
      BEGIN
         FOR x IN 0..num_rows LOOP
            outrow.x := x;
            PIPE ROW(outrow);
 
         END LOOP;
         RETURN;
      END;
END;
/

Enjoy!

Jules