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) );<br /><br />insert into t1 values ( 'aaaa', 'col1', 3 );<br />insert into t1 values ( 'aaaa', 'col2', 13 );<br />insert into t1 values ( 'aaaa', 'col3', 6 );<br />insert into t1 values ( 'bbbb', 'col1', 2 );<br />insert into t1 values ( 'bbbb', 'col2', 23 );<br />insert into t1 values ( 'bbbb', 'col3', 123 );<br />insert into t1 values ( 'cccc', 'col1', 241 );<br />insert into t1 values ( 'cccc', 'col2', 0 );<br />insert into t1 values ( 'cccc', 'col3', 36 );

The usual way to pivot this data is:

select c1<br />     , max( decode( c2, 'col1', c3 ) ) col1<br />     , max( decode( c2, 'col2', c3 ) ) col2<br />     , max( decode( c2, 'col3', c3 ) ) col3<br />from t1<br />group by c1

C1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COL1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COL2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COL3<br />---------- ---------- ---------- ----------<br />aaaa&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6<br />bbbb&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 23&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 123<br />cccc&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 241&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 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&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COL1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COL2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COL3<br />---------- ---------- ---------- ----------<br />bbbb&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 23&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 123<br />cccc&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 241&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 36<br />aaaa&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6

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

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

And now again with parametervalue equal to 7:

<br />SQL&gt; select * from table( pivotimpl.show(&nbsp; 7 ) );<br />C1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COL1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COL2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COL3<br />---------- ---------- ---------- ----------<br />bbbb&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 23&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 123<br />cccc&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 241&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 36<br />aaaa&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 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