Oracle Pivoting/Transforming Rows to Columns

Hi,

Not sure if anyone will be able to help, but, I'm foraying into Oracle territory and have written a simple (hacked together) function to transform some data so that row data can become column headers.

A  B  C
=====
X  G  1
Y  G  2
X  H  1

To:

A  G  H
=====
X  1  1
Y  2  0

The code basically rewrites the query so that I'm using decode to to derive the column data. then I have the result being passed out of the function as a ref_cursor (loose). But I can't use this in an sql statement afterwards.

Anyone got any ideas on how to improve this or a better way of achieving the goal?

Thanks,

Keoghan

[size=2][color=#0000f0]
CREATE[/color][/size][size=2][color=#000000] [/color][/size][size=2][color=#0000f0]OR[/color][/size][size=2][color=#000000] [/color][/size][size=2][color=#0000f0]REPLACE[/color][/size][size=2][color=#000000] [/color][/size][size=2][color=#0000f0]PACKAGE[/color][/size][size=2][color=#000000] [/color][/size][size=2][color=#0000f0]BODY[/color][/size][size=2][color=#000000] [/color][/size][size=2][color=#808000]pkg_pivot[/color][/size][size=2][color=#000000] [/color][/size][size=2][color=#0000f0]AS
[/color][/size][size=2]
[/size][size=2][color=#0000f0]FUNCTION[/color][/size][size=2] [/size][size=2][color=#808000]pivot[/color][/size][size=2][color=#0000f0]([/color][/size][size=2]str_sql [/size][size=2][color=#ff0000]varchar2[/color][/size][size=2][color=#0000f0],[/color][/size][size=2] pivot_column [/size][size=2][color=#ff0000]varchar2[/color][/size][size=2][color=#0000f0],[/color][/size][size=2] value_column [/size][size=2][color=#ff0000]varchar2[/color][/size][size=2][color=#0000f0],[/color][/size][size=2] pivot_method [/size][size=2][color=#ff0000]varchar2[/color][/size][size=2][color=#0000f0])[/color][/size][size=2] [/size][size=2][color=#0000f0]RETURN[/color][/size][size=2] sys_refcursor [/size][size=2][color=#0000f0]IS[/color][/size][size=2] 

rc sys_refcursor[/size][size=2][color=#0000f0];
[/color][/size][size=2]str_tsql [/size][size=2][color=#ff0000]varchar2[/color][/size][size=2][color=#0000f0]([/color][/size][size=2][color=#800000]5000[/color][/size][size=2][color=#0000f0]);
[/color][/size][size=2]str_rsql [/size][size=2][color=#ff0000]varchar2[/color][/size][size=2][color=#0000f0]([/color][/size][size=2][color=#800000]5000[/color][/size][size=2][color=#0000f0]);
[/color][/size][size=2]
[/size][size=2][color=#0000f0]type[/color][/size][size=2] arry [/size][size=2][color=#0000f0]is[/color][/size][size=2] [/size][size=2][color=#0000f0]table[/color][/size][size=2] [/size][size=2][color=#0000f0]of[/color][/size][size=2] [/size][size=2][color=#ff0000]varchar2[/color][/size][size=2][color=#0000f0]([/color][/size][size=2][color=#800000]20[/color][/size][size=2][color=#0000f0]);
[/color][/size][size=2]v_cols arry[/size][size=2][color=#0000f0];
[/color][/size][size=2]
[/size][size=2][color=#0000f0]BEGIN
[/color][/size][size=2]
[/size][i][size=2][color=#008000]-- first we need to get all the values of the column which we're pivoting by...
[/i][/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0]:=[/color][/size][size=2]str_sql[/size][size=2][color=#0000f0];
[/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0]:=REPLACE([/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0],[/color][/size][size=2]SUBSTR[/size][size=2][color=#0000f0]([/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0],[/color][/size][size=2][color=#800000]8[/color][/size][size=2][color=#0000f0],[/color][/size][size=2]INSTR[/size][size=2][color=#0000f0]([/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0],[/color][/size][size=2][color=#ff0000]'FROM '[/color][/size][size=2][color=#0000f0])-[/color][/size][size=2][color=#800000]9[/color][/size][size=2][color=#0000f0]),[/color][/size][size=2]pivot_column[/size][size=2][color=#0000f0]);
[/color][/size][size=2]
[/size][size=2][color=#0000f0]IF[/color][/size][size=2] INSTR[/size][size=2][color=#0000f0]([/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0],[/color][/size][size=2][color=#ff0000]'GROUP BY'[/color][/size][size=2][color=#0000f0])!=[/color][/size][size=2][color=#800000]0[/color][/size][size=2] [/size][size=2][color=#0000f0]THEN
[/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0]:=REPLACE([/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0],[/color][/size][size=2]SUBSTR[/size][size=2][color=#0000f0]([/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0],[/color][/size][size=2]INSTR[/size][size=2][color=#0000f0]([/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0],[/color][/size][size=2][color=#ff0000]'GROUP BY '[/color][/size][size=2][color=#0000f0])+[/color][/size][size=2][color=#800000]9[/color][/size][size=2][color=#0000f0]),[/color][/size][size=2]pivot_column[/size][size=2][color=#0000f0]);[/color][/size][size=2] 
[/size][size=2][color=#0000f0]ELSE[/color][/size][size=2] 
str_tsql[/size][size=2][color=#0000f0]:=[/color][/size][size=2]str_tsql || [/size][size=2][color=#ff0000]' GROUP BY '[/color][/size][size=2] || pivot_column[/size][size=2][color=#0000f0];[/color][/size][size=2] 
[/size][size=2][color=#0000f0]END[/color][/size][size=2] [/size][size=2][color=#0000f0]IF;
[/color][/size][size=2]
[/size][size=2][color=#0000f0]EXECUTE[/color][/size][size=2] [/size][size=2][color=#0000f0]IMMEDIATE[/color][/size][size=2] str_tsql [/size][size=2][color=#0000f0]BULK[/color][/size][size=2] [/size][size=2][color=#0000f0]COLLECT[/color][/size][size=2] [/size][size=2][color=#0000f0]INTO[/color][/size][size=2] v_cols[/size][size=2][color=#0000f0];
[/color][/size][size=2]
str_tsql[/size][size=2][color=#0000f0]:=[/color][/size][size=2]str_sql[/size][size=2][color=#0000f0];
[/color][/size][size=2]
[/size][i][size=2][color=#008000]--we need to modify the query...
[/i][/color][/size][size=2]
[/size][size=2][color=#0000f0]FOR[/color][/size][size=2] i [/size][size=2][color=#0000f0]IN[/color][/size][size=2] [/size][size=2][color=#800000]1[/color][/size][size=2][color=#0000f0]..[/color][/size][size=2]v_cols[/size][size=2][color=#0000f0].[/color][/size][size=2]count [/size][size=2][color=#0000f0]LOOP
[/color][/size][size=2]str_rsql[/size][size=2][color=#0000f0]:=[/color][/size][size=2]str_rsql || pivot_method || [/size][size=2][color=#ff0000]'(DECODE('[/color][/size][size=2] || pivot_column || [/size][size=2][color=#ff0000]','''[/color][/size][size=2] || v_cols[/size][size=2][color=#0000f0]([/color][/size][size=2]i[/size][size=2][color=#0000f0])[/color][/size][size=2] || [/size][size=2][color=#ff0000]''','[/color][/size][size=2] || value_column || [/size][size=2][color=#ff0000]',0)) "'[/color][/size][size=2]|| v_cols[/size][size=2][color=#0000f0]([/color][/size][size=2]i[/size][size=2][color=#0000f0])[/color][/size][size=2] || [/size][size=2][color=#ff0000]'",'[/color][/size][size=2][color=#0000f0];
[/color][/size][size=2][/size][size=2][color=#0000f0]END[/color][/size][size=2] [/size][size=2][color=#0000f0]LOOP;
[/color][/size][size=2]
str_rsql[/size][size=2][color=#0000f0]:=[/color][/size][size=2]substr[/size][size=2][color=#0000f0]([/color][/size][size=2]str_rsql[/size][size=2][color=#0000f0],[/color][/size][size=2][color=#800000]1[/color][/size][size=2][color=#0000f0],[/color][/size][size=2]length[/size][size=2][color=#0000f0]([/color][/size][size=2]str_rsql[/size][size=2][color=#0000f0])-[/color][/size][size=2][color=#800000]1[/color][/size][size=2][color=#0000f0]);
[/color][/size][size=2][/size][i][size=2][color=#008000]--replace the pivot and value columns with the new sql...
[/i][/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0]:=REPLACE([/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0],[/color][/size][size=2]pivot_column||[/size][size=2][color=#ff0000]','[/color][/size][size=2][color=#0000f0],[/color][/size][size=2][color=#ff0000]''[/color][/size][size=2][color=#0000f0]);
[/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0]:=REPLACE([/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0],[/color][/size][size=2][color=#ff0000]','[/color][/size][size=2]||pivot_column[/size][size=2][color=#0000f0],[/color][/size][size=2][color=#ff0000]''[/color][/size][size=2][color=#0000f0]);
[/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0]:=REPLACE([/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0],[/color][/size][size=2][color=#ff0000]', '[/color][/size][size=2]||pivot_column[/size][size=2][color=#0000f0],[/color][/size][size=2][color=#ff0000]''[/color][/size][size=2][color=#0000f0]);
[/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0]:=REPLACE([/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0],[/color][/size][size=2]pivot_column[/size][size=2][color=#0000f0],[/color][/size][size=2][color=#ff0000]''[/color][/size][size=2][color=#0000f0]);
[/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0]:=REPLACE([/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0],[/color][/size][size=2]value_column||[/size][size=2][color=#ff0000]','[/color][/size][size=2][color=#0000f0],[/color][/size][size=2]str_rsql[/size][size=2][color=#0000f0]);
[/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0]:=REPLACE([/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0],[/color][/size][size=2][color=#ff0000]','[/color][/size][size=2]||value_column[/size][size=2][color=#0000f0],[/color][/size][size=2]str_rsql[/size][size=2][color=#0000f0]);
[/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0]:=REPLACE([/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0],[/color][/size][size=2][color=#ff0000]', '[/color][/size][size=2]||value_column[/size][size=2][color=#0000f0],[/color][/size][size=2]str_rsql[/size][size=2][color=#0000f0]);
[/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0]:=REPLACE([/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0],[/color][/size][size=2]value_column[/size][size=2][color=#0000f0],[/color][/size][size=2]str_rsql[/size][size=2][color=#0000f0]);
[/color][/size][size=2]
[/size][size=2][color=#0000f0]OPEN[/color][/size][size=2] rc [/size][size=2][color=#0000f0]FOR[/color][/size][size=2] str_tsql[/size][size=2][color=#0000f0];
[/color][/size][size=2]
[/size][size=2][color=#0000f0]RETURN[/color][/size][size=2] rc[/size][size=2][color=#0000f0];
[/color][/size][size=2]
[/size][size=2][color=#0000f0]EXCEPTION
[/color][/size][size=2]
[/size][size=2][color=#0000f0]WHEN[/color][/size][size=2] [/size][size=2][color=#ff0000]OTHERS[/color][/size][size=2] [/size][size=2][color=#0000f0]THEN
[/color][/size][size=2][/size][size=2][color=#0000f0]raise_application_error[/color][/size][size=2] [/size][size=2][color=#0000f0](-[/color][/size][size=2][color=#800000]20001[/color][/size][size=2][color=#0000f0],[/color][/size][size=2]str_tsql[/size][size=2][color=#0000f0]);
[/color][/size][size=2][/size][size=2][color=#0000f0]NULL;
[/color][/size][size=2]
[/size][size=2][color=#0000f0]END[/color][/size][size=2] [/size][size=2][color=#808000]pivot[/color][/size][size=2][color=#0000f0];
[/color][/size][size=2]
[/size][size=2][color=#0000f0]END[/color][/size][size=2][color=#000000] [/color][/size][size=2][color=#808000]pkg_pivot[/color][/size][size=2][color=#0000f0];
/
[/color][/size]