Pivot dynamic data with pipelined function

9

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

Share.

About Author

9 Comments

  1. Really useful piece of code:
    But in the precise usage I have for it, it is failing! for example

    “Select count(*) from Table( pivot (‘select compound, method,RESULT from
    ALLRES_XXXX_VIEW3 where
    resultname = ”EC50”’))”

    ERROR at line 1:
    ORA-00902: invalid datatype.

    If works fine if ‘method’ is not used as the pivoting element ( the column heading). Method is a concatentated string coming from another view. Is the concatenation the problem? Do I need to materialiase my views?

    Judy

  2. ODCITableDescribe is only called when your SQL-statement (pivotimpl.show) is not in the SGA. The different aurgument makes the SQL-statement different, ODCITableDescribe is called and the number of columns in the output can change.

  3. Hi
    For the function pivotimpl.show( 8 ), pivotimpl.show( 7 ) what does this argument do? I see its returning a dummy number but the columns and results change based on the argument you are passing. Can anyone explain me this.

  4. I know of reason why it would not work on 9i. However, I have not tested it. Testing it would be little effort for you if you have a 9i database at your disposal – please let us know the outcome of that test.

    best regards,

    Lucas

  5. I agree with Mr. Ed and in accounting departments, reports are always presented that way. The number of colums is dynamic. Though this can be done using Oracle Reports Builder, a handy query like this is a good alternative since you can’t always use reports to show your data.

    My question is, does pipelined function work with 9i? I looked at pivot.sql and it noticed there’s quite a number of lines required to make this thing work. But anyhow, this is an amazing stuff!

  6. Niice entry! I finally got the time to read this completely and I can immediately find uses for this in the DW. Now I have to do some performance testing.

    LewisC

  7. Why pivot? Because this:

    Jan Feb Mar
    US $100 $300 $500
    Austria $130 $80 $110
    Mexico $200 $250 $400

    Looks a lot better (easier to read, more intuitive) than this:

    (US,Jan,$100)
    (US,Feb,$300)
    (US,Mar,$500)
    (Austria,Jan,$130)
    (Austria,Feb,$80)
    (Austria,Mar,$110)
    (Mexico,Jan,$200)
    (Mexico,Feb,$250)
    (Mexico,Mar,$400)

    2-dimensional data just looks better in this simple format.

    Some part has to do the pivot. In many cases, it’s most convenient in the database layer.