oracle动态交叉表
有一张表
IDNAMEQUARTERQUANTITY
1开发部春天10000
2开发部夏天50000
3开发部秋天30000
4开发部冬天20000
5销售部春天10000
6销售部夏天4000
7销售部秋天30000
8销售部冬天20000
想要转换为
QUARTER开发部销售部
夏天500004000
春天1000010000
秋天3000030000
冬天2000020000
如果已知部门可以写静态sql：
declarecv_sql varchar2(5000):=' ';cn_number number;cn_i number :=0;sql_query varchar2(5000);begin for v_cur in (select distinct name into cn_number from department)loopcv_sql:= cv_sql||',sum(decode(name,'''|| v_cur.name ||''',quantity,0)) '|| v_cur.name;end loop;sql_query:='create or replace view query_zjx_v as select quarter'||cv_sql||' from department group by quarter';dbms_output.put_line(sql_query);execute immediate sql_query;end;
