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
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
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.
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.
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
Does it work on Oracle 9i?
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!
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
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.
Anton, like they said on OTN, this is really amazing stuff. Cool!
Note: I got it to work on 10gR2, but not on 10gR1 (10.1.0.2)