Pivot dynamic data with pipelined function 13422386 1019544571447648 7687716130941590224 o1

Pivot dynamic data with pipelined function

Many people want to pivot the result of their queries. I have no idea why, but they do.
For instance,

create table t1( c1 varchar2(10), c2 varchar2(10), c3 number(5) );

insert into t1 values ( 'aaaa', 'col1', 3 );
insert into t1 values ( 'aaaa', 'col2', 13 );
insert into t1 values ( 'aaaa', 'col3', 6 );
insert into t1 values ( 'bbbb', 'col1', 2 );
insert into t1 values ( 'bbbb', 'col2', 23 );
insert into t1 values ( 'bbbb', 'col3', 123 );
insert into t1 values ( 'cccc', 'col1', 241 );
insert into t1 values ( 'cccc', 'col2', 0 );
insert into t1 values ( 'cccc', 'col3', 36 );

The usual way to pivot this data is:

select c1
     , max( decode( c2, 'col1', c3 ) ) col1
     , max( decode( c2, 'col2', c3 ) ) col2
     , max( decode( c2, 'col3', c3 ) ) col3
from t1
group by c1
C1               COL1       COL2       COL3
---------- ---------- ---------- ----------
aaaa                3         13          6
bbbb                2         23        123
cccc              241          0         36

This method has one disadvantage, all possible values of column c2 are hardcoded in the query. If a new record with a different value for c2 is inserted the query gives the same result.

A “normal” pipelined function can not be used to pivot data, as the resultset of a “normal” pipelined function has alwasy the same number (and type) of columns.
But on Oracle 10gR2 it is possible to implement a pipelined function with the “interface”-method. For this method a object type is created, which implements the pipelined function.

The function is used as follows:

SQL> select * from table( pivotimpl.show(  7 ) );
C1               COL1       COL2       COL3
---------- ---------- ---------- ----------
bbbb                2         23        123
cccc              241          0         36
aaaa                3         13          6

Now if we add a row to the table, that should show up as a new column in our pivot-result:

SQL> insert into t1 values ( 'cccc', 'col6', 2 );
SQL> select * from table( pivotimpl.show( 8 ) );
C1               COL1       COL2       COL3       COL6
---------- ---------- ---------- ---------- ----------
bbbb                2         23        123
cccc              241          0         36          2
aaaa                3         13          6

And now again with parametervalue equal to 7:

SQL> select * from table( pivotimpl.show(  7 ) );
C1               COL1       COL2       COL3
---------- ---------- ---------- ----------
bbbb                2         23        123
cccc              241          0         36
aaaa                3         13          6

Note how the output changes, depending on the parameter of the function. It appears that the complete text of the query is cached somewhere. Any changes in values of column c2 are only detected of the calling statement changes. And a extra space somewhere in the query is enough!. Changes in columns c1 or c3 have immediate impact on the results.

Download Sources: pivot.sql

(I have changed the code, now it works also on 10gR1)

Anton

9 Comments

  1. Judy December 23, 2008
  2. anton June 19, 2008
  3. Hari February 29, 2008
  4. Lucas Jellema July 27, 2006
  5. Tarun Sharma July 26, 2006
  6. MikeMM July 10, 2006
  7. LewisC June 3, 2006
  8. Mr. Ed May 17, 2006
  9. Lucas Jellema May 16, 2006