以前写的 动态行转列 其实行列转换类型都差不多 create or replace procedure p1 is Cursor cursor is select t.column_name cn from user_col_comments t where t.table_name = upper('t1') and t.column_name <> 'y'; sqlstr varchar2(4000); begin for c_name in cursor loop begin sqlstr := sqlstr + ' select '||c_name.cn||',decode(t1.y,2009,(select '||c_name.cn||' from t1 where y=2009),0) cc from t1 union all '; DBMS_OUTPUT.PUT_LINE(sqlstr); end; end loop; --EXECUTE IMMEDIATE (sqlstr); end p1;
貌似pivot xml好像也可以 但是返回的是xml
create or replace procedure p1 is
Cursor cursor is select t.column_name cn
from user_col_comments t
where t.table_name = upper('t1') and t.column_name <> 'y';
sqlstr varchar2(4000);
begin
for c_name in cursor loop
begin
sqlstr := sqlstr + ' select '||c_name.cn||',decode(t1.y,2009,(select '||c_name.cn||' from t1 where y=2009),0) cc from t1 union all ';
DBMS_OUTPUT.PUT_LINE(sqlstr);
end;
end loop;
--EXECUTE IMMEDIATE (sqlstr);
end p1;