pivot & unpivot 11g新特性
1pivot
以列-值对的形式出现,典型的行转列报表函数。
createtabletest_demo(idint,namevarchar(20),numsint);----创建表 insertintotest_demovalues(1,'苹果',1000); insertintotest_demovalues(2,2000); insertintotest_demovalues(3,4000); insertintotest_demovalues(4,'橘子',5000); insertintotest_demovalues(5,3000); insertintotest_demovalues(6,'葡萄',3500); insertintotest_demovalues(7,'芒果',4200); insertintotest_demovalues(8,5500); commit; selectname,sum(nums) fromtest_demo groupbyname; select* from(selectname,numsfromtest_demo) pivot(sum(nums) fornamein('苹果','芒果')); sql>select* 2from(selectname,numsfromtest_demo) 3pivot(sum(nums) 4fornamein('苹果'as"苹果",'芒果'));--别名使用 苹果'橘子''葡萄''芒果' ---------------------------------------- 7000800035009700
这里再说语法:
pivot聚合函数 for 列名 in 类型 ,其中 in 中可以指定别名,in中还可以指定子查询,比如 select distinct code from customers
2unpivot
典型的列转行报表函数
createtableFruit(idint,Q1int,Q2int,Q3int,Q4int); 这里Q1int,Q4int表示四季度。 insertintoFruitvalues(1,1000,2000,3300,5000); insertintoFruitvalues(2,3000,3200,1500); insertintoFruitvalues(3,'香蕉',2500,3500,2200,2500); insertintoFruitvalues(4,1500,1200,3500); commit; select*fromFruit; selectid,name,quarter,sellfromFruitunpivot(sellforquarterin(q1,q2,q3,q4));
注意:unpivot没有聚合函数,quarter、sell字段也是临时的变量。
这里sell是统计值,quarter表示季度及类型。
执行结果:
sql>selectid,q4)); IDNAMEQUARTERSELL --------------------------------------------------------------------------------------------------------- 1苹果Q11000 1苹果Q22000 1苹果Q33300 1苹果Q45000 2橘子Q13000 2橘子Q23000 2橘子Q33200 2橘子Q41500 3香蕉Q12500 3香蕉Q23500 3香蕉Q32200 3香蕉Q42500 4葡萄Q11500 4葡萄Q22500 4葡萄Q31200 4葡萄Q43500