欢迎技术交流。 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;
/