Oracle通用行转列方法,适合报表统计等

2016/12/29 15:38
阅读数 589
create or replace function row_to_col_func(tabname in varchar2,--tabname 需要进行行转列操作的表名;
                                  group_col in varchar2,--group_col 查询结果要按某列或某些列分组的字段名;
                                  column_col in varchar2,--column_col 要从行转成列的字段;;
                                  value_col in varchar2,--value_col 需要聚合的值字段;
                                  Aggregate_func in varchar2 default 'max',--Aggregate_func 选用的聚合函数,可选,默认为max;
                                  colorder in varchar2 default null,--colorder 行转列后列的排序,可选;
                                  roworder in varchar2 default null,--roworder 行转列后记录的排序,可选;
                                  when_value_null in varchar2 default null--when_value_null 若value_col字段的值聚合后为空,则转换成该值,可选;
                                  )return sys_refcursor
Authid Current_User
as
  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','sum','dept','1,2','0') from dual;

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部