/*
标志代码 错误代码 错误数量
------- ----------- -----------
1002 77 12
1235 87 10
1568 77 23
1869 101 5(4 行受影响)
*///要转换成如下
/*
标志代码 77 87 101
------- ----------- ----------- -----------
1002 12 0 0
1235 0 10 0
1568 23 0 0
1869 0 0 5(4 行受影响)
*/错误代码是不确定的,如何写出动态语句啊?谢谢
参考一下
http://topic.csdn.net/u/20091019/11/67cd55a3-3f42-4db7-a3f8-91dd52a913cd.html?2339
http://topic.csdn.net/u/20100109/13/6a10c168-f190-4766-b838-adbf03c4ac7b.html?64786
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
)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;