ORACLE 行轉列的通用過程

標籤：http   color   io   os   ar   for   資料   sp   cti   

--測試資料
create table rowtocol_test as
select 2009 year,1 month,‘部門1‘ dept,50000 expenditure from dual
union all select 2009,2,‘部門1‘,20000 from dual
union all select 2009,2,‘部門1‘,30000 from dual
union all select 2010,1,‘部門1‘,35000 from dual
union all select 2009,2,‘部門2‘,40000 from dual
union all select 2009,3,‘部門2‘,25000 from dual
union all select 2010,2,‘部門3‘,60000 from dual
union all select 2009,2,‘部門3‘,15000 from dual
union all select 2009,2,‘部門3‘,10000 from dual;

--建預存程序 返回遊標
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
/*
參數：
tabname 需要進行行轉列操作的表名；
group_col 查詢結果要按某列或某些列分組的欄位名；
column_col 要從行轉成列的欄位；
value_col 需要彙總的值欄位；
Aggregate_func 選用的彙總函式，可選，預設為max；
colorder 行轉列後列的排序，可選；
roworder 行轉列後記錄的排序，可選；
when_value_null 若value_col欄位的值彙總後為空白，則轉換成該值，可選；
--viewname 建立的視圖名稱，可選，預設為v_tmp。
*/
sqlstr varchar2(2000):=‘select ‘||group_col||‘ ‘;
c1 sys_refcursor;
v1 varchar2(100);
cur sys_refcursor;
begin
open c1 for ‘select distinct ‘||column_col||‘ from ‘||tabname||case when colorder is not null then ‘ order by ‘||colorder end;
loop
fetch c1 into v1;
exit when c1%notfound;
sqlstr:=sqlstr||chr(10)||‘,‘||case when when_value_null is not null then ‘nvl(‘ end||
Aggregate_func||‘(decode(to_char(‘||column_col||‘),‘‘‘||v1||‘‘‘,‘||value_col||‘))‘||
case when when_value_null is not null then chr(44) ||when_value_null||chr(41) end||‘"‘||v1||‘"‘;
end loop;
close c1;
open cur for sqlstr||‘ from ‘||tabname||‘ group by ‘||group_col||case when roworder is not 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 => ‘1,2‘,when_value_null => ‘0‘)
from dual;

 

 

轉自：http://bbs.csdn.net/topics/330039676

ORACLE 行轉列的通用過程