行列转换--例子

create table tmp( counter varchar(20), subject varchar(20), class varchar(20) ); insert into tmp values('36','eng','one'); insert into tmp values('44','eng','two'); insert into tmp values('44','mat','two'); insert into tmp values('33','chi','one'); insert into tmp values('39','chi','three'); commit; 转换后： one two three eng 36 44 0 mat 0 44 0 chi 33 0 39 set serveroutput on; declare tabname varchar2(20):='tmp';--'XXX'分别用相应的表名和字段名代替 col1 varchar2(10):='subject'; col2 varchar2(10):='class'; col3 varchar2(10):='counter'; toTblname varchar2(10):= 'tmp_dyn'; sqlstr varchar2(2000):='create table '||toTblname||' as select '||col1||' '; c1 sys_refcursor; v1 varchar2(100); begin open c1 for 'select distinct to_char('||col2||') from '||tabname; loop fetch c1 into v1; exit when c1%notfound; sqlstr:=sqlstr||' ,nvl(max(decode('||col2||','''||v1||''','||col3||')),0) as class'||v1; end loop; close c1; sqlstr:=sqlstr||' from '||tabname||' group by '||col1; execute immediate sqlstr; dbms_output.put_line(sqlstr); end; / create table tmp_sta as select subject ,nvl(max(decode(class,'three',counter)),0) three ,nvl(max(decode(class,'one',counter)),0) one ,nvl(max(decode(class,'two',counter)),0) two from tmp group by subject; create table tmp_fun as --11g select * from tmp pivot( max(counter) for class in ('one' as one, 'two' as two, 'three' as three ) ) order by subject; Conversely, create table tmp_ret_sta as select * from ( select subject,'one' class,one counter from tmp_sta union all select subject,'two' class,two counter from tmp_sta union all select subject,'three' class,three counter from tmp_sta ) where counter <> 0; create table tmp_ret_fun as --11g select * from tmp_fun unpivot( counter for class in (one, two, three ) ) where counter<>0 order by subject;