oracle列转行 空行,Oracle 行转列（不固定行数的行转列，动态）（转）

SQLSERVER :行列转换例子：  http://www.cnblogs.com/gaizai/p/3753296.html#_labelFour

1. Oracle 11g之后新增了行列转换的函数 pivot 和 unpivot大大简化了行列转换处理。

2. 在Oracle 10g及以前版本，一般是通过各种SQL进行行列转换，列入下面例子：

create or replace procedure P_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 P_row_to_col;

--测试数据

create table rowtocol_test as

select 2009 year,1 month,'AAA1' dept,50000 ex