Oracle 行转列----存储过程

欢迎技术交流。 QQ：138986722

原文地址--http://topic.csdn.net/u/20100109/13/6a10c168-f190-4766-b838-adbf03c4ac7b.html?39758

--行转列过程
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',  --选用的聚合函数，可选，默认为max
                                       colorder        in varchar2 default null,   --行转列后列的排序，可选
                                       roworder        in varchar2 default null,   --行转列后记录的排序，可选
                                       when_value_null in varchar2 default '0',   --若value_col字段的值聚合后为空，则转换成该值，可选
                                       viewname        in varchar2 default 'v_tmp' --创建的视图名称，可选，默认为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;
/