Oracle行转列通用过程

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;