General procedures for ORACLE row-to-column

--Test data
CREATE TABLE Rowtocol_test as
Select year,1 Month, ' Department 1 ' dept,50000 expenditure from dual
UNION ALL Select 2009, 2, ' Department 1 ', 20000 from dual
UNION ALL Select 2009, 2, ' Department 1 ', 30000 from dual
UNION ALL Select 2010, 1, ' Department 1 ', 35000 from dual
UNION ALL Select 2009, 2, ' Department 2 ', 40000 from dual
UNION ALL Select 2009, 3, ' Department 2 ', 25000 from dual
UNION ALL Select 2010, 2, ' Department 3 ', 60000 from dual
UNION ALL Select 2009, 2, ' Department 3 ', 15000 from dual
UNION ALL Select 2009, 2, ' Department 3 ', 10000 from dual;

--Build stored procedure return cursor
Create or Replace function Row_to_col_func (tabname in Varchar2,
Group_col in Varchar2,
Column_col in Varchar2,
Value_col in Varchar2,
Aggregate_func in varchar2 default ' Max ',
Colorder in varchar2 default NULL,
Roworder in varchar2 default NULL,
When_value_null in varchar2 default null
) Return Sys_refcursor
Authid Current_User
As
/*
Parameters:
TabName the name of the table that requires a row-to-column operation;
Group_col the name of the field to which the query results are grouped by a column or column;
Column_col the field to be converted from row to column;
Value_col value fields that need to be aggregated;
Aggregate_func selected aggregate function, optional, the default is Max;
Colorder column sorting after row to column, optional;
Roworder the sorting of records after row to column, optional;
When_value_null if the value of the Value_col field is aggregated and empty, it is converted to that value, optional;
--viewname created view name, optional, default is v_tmp.
*/
Sqlstr varchar2 (+): = ' SELECT ' | | group_col| | ' ‘;
C1 Sys_refcursor;
V1 VARCHAR2 (100);
Cur sys_refcursor;
Begin
Open C1 for ' select DISTINCT ' | | column_col| | ' From ' | | tabname| | Case where Colorder is isn't null then ' ORDER BY ' | | Colorder end;
Loop
Fetch C1 into V1;
Exit when C1%notfound;
sqlstr:=sqlstr| | Chr (10) | | ', ' | | Case when When_value_null was not null and then ' NVL (' end| |
aggregate_func| | ' (Decode (to_char (' | | | column_col| | '), ' | | v1| | ', ' | | value_col| | ')) ' | |
Case when When_value_null was not null and then Chr (44) | | when_value_null| | Chr (end|) | | v1| | ' ";
End Loop;
Close C1;
Open cur for sqlstr| | "from" | | tabname| | ' GROUP BY ' | | group_col| | Case where Roworder is isn't null then ' ORDER BY ' | | Roworder end;
return cur;
End Row_to_col_func;

--
Select Row_to_col_func (' rowtocol_test ', ' Year,month ', ' dept ', ' expenditure ', Aggregate_func = ' Sum ', Colorder = ' Dept ', Roworder = ', when_value_null ' 0 ')
from dual;

Transferred from: http://bbs.csdn.net/topics/330039676

General procedures for ORACLE row-to-column