>Oracle11g新特性_Pivot 和Unpivot
>
我从Oracle 数据库 11g： 面向 DBA 和开发人员的重要新特性
http://www.oracle.com/technology/global/cn/pub/articles/oracle-database-11g-top-features/index.html
测试了一些个人觉得对日常工作有用的特性进行测试。
需要详细信息请猛击上方链接或者查阅相关文档。
3.Pivot为SQL语言增添了一个非常重要且实用的功能。
可以使用pivot函数针对任何关系表创建一个交叉表报表，
而不必编写包含大量decode函数的令人费解的、不直观的代码。
同样，您可以使用unpivot操作转换任何交叉表报表，以常规关系表的形式对其进行存储。
CREATE TABLE CUSTOMER
(
CUST_ID NUMBER(10),
CUST_NAME VARCHAR2(20),
STATE_CODE VARCHAR2(20),
TIMES_PURCHASED NUMBER(3)
) nologging
;
insert into customer(cust_id,cust_name,state_code,times_purchased) values
(1,’sfsf’,'beijing’,1);
insert into customer(cust_id,cust_name,state_code,times_purchased) values
(2,’test’,'beijing’,3);
insert into customer(cust_id,cust_name,state_code,times_purchased) values
(3,’ayyt’,'beijing’,1);
insert into customer(cust_id,cust_name,state_code,times_purchased) values
(4,’bird’,'tianjin’,1);
insert into customer(cust_id,cust_name,state_code,times_purchased) values
(5,’call’,'shanghai’,1);
insert into customer(cust_id,cust_name,state_code,times_purchased) values
(6,’dogr’,'shanghai’,4);
insert into customer(cust_id,cust_name,state_code,times_purchased) values
(7,’elly’,'chongqing’,1);
insert into customer(cust_id,cust_name,state_code,times_purchased) values
(8,’febr’,'chongqing’,1);
commit;
想得到如下结果：
. TIMES_PURCHASED ‘beijing’ ‘tianjin’ ‘shanghai’ ‘chongqing’
————— ———- ———- ———- ———-
1 2 1 1 2
3 1 0 0 0
4 0 0 1 0
在11g之前的sql写法为：
SELECT t.times_purchased,
SUM(decode(t.state_code,
‘beijing’,
1,
’0′)) AS beijing,
SUM(decode(t.state_code,
‘tianjin’,
1,
’0′)) AS beijing,
SUM(decode(t.state_code,
‘shanghai’,
1,
’0′)) AS beijing,
SUM(decode(t.state_code,
‘chongqing’,
1,
’0′)) AS beijing
FROM customer t
GROUP BY t.times_purchased
ORDER BY t.times_purchased;
在11g中，可以用如下sql：
SELECT *
FROM (SELECT times_purchased,
state_code
FROM customer t) pivot(COUNT(state_code) FOR state_code IN(‘beijing’,
‘tianjin’,
‘shanghai’,
‘chongqing’))
ORDER BY times_purchased
pivot 的反向操作 UNPIVOT，将列打乱变为行。