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