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

典型的列转行报表函数

@H_403_70@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表示季度及类型。

执行结果：

@H_403_70@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