行转列通用存储过程

行转列通用存储过程

1.创建行转列通用存储过程

SQL代码：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;

相关参数说明：

tabname 需要进行行转列操作的表名，视图也可以；
group_col 查询结果要按某列或某些列分组的字段名；
column_col 要从行转成列的字段；
value_col 需要聚合的值字段；
Aggregate_func 选用的聚合函数，可选，默认为max；
colorder 行转列后列的排序，可选；
roworder 行转列后记录的排序，可选；
when_value_null 若value_col字段的值聚合后为空，则转换成该值，可选；
viewname 创建的视图名称，可选，默认为v_tmp。

 

 

2.创建测试数据

SQL代码：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;

 

 

3.验证

SQL代码：execute row_to_col('rowtocol_test','year,month','dept','expenditure',Aggregate_func => 'sum',colorder => 'dept',roworder => '1,2',when_value_null => '0');

 

 

4.扩展：对于视图的测试

SQL代码：create view view_rowtocol as select * from rowtocol_test where year=2009;

                    execute row_to_col('view_rowtocol','year,month','dept','expenditure',Aggregate_func => 'sum',colorder => 'dept',roworder => '1,2',when_value_null => '0');