create or replace procedure row_to_col(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, viewname in varchar2 default 'v_tmp') Authid Current_User as sqlstr varchar2(2000):='create or replace view '||viewname||' as select '||group_col||' '; c1 sys_refcursor; v1 varchar2(100); 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; sqlstr:=sqlstr||' from '||tabname||' group by '||group_col||case when roworder is not null then ' order by '||roworder end; execute immediate sqlstr; end row_to_col;
行列转换而已,不管是否固定,参考通过方法:http://topic.csdn.net/u/20100109/13/6a10c168-f190-4766-b838-adbf03c4ac7b.html?93286
create or replace procedure row_to_col(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,
viewname in varchar2 default 'v_tmp')
Authid Current_User
as
sqlstr varchar2(2000):='create or replace view '||viewname||' as select '||group_col||' ';
c1 sys_refcursor;
v1 varchar2(100);
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;
sqlstr:=sqlstr||' from '||tabname||' group by '||group_col||case when roworder is not null then ' order by '||roworder end;
execute immediate sqlstr;
end row_to_col;