首先，我们来看看Oracle对于其的解释：
可见，pivot是数据仓库中的关键技术，它利用交叉查询(crosstabulation query)将行转换为列。
基本语法如下：
SELECT.... FROM<table-expr> PIVOT ( aggregate-function(<column>) FOR<pivot-column>IN(<value1>,<value2>,...,<valuen>) )AS<alias> WHERE.....
下面我们来通过具体的案例对其进行阐述。
首先，构造案例所需的数据，
1> 创建视图，以EMP表的数据作为源数据。
CREATEVIEWemp_viewAS SELECT deptno,job,to_char(hiredate,'yyyy')hiredate, count(*)cnt,sum(sal)sum_sal FROMemp GROUPBYdeptno,job,to_char(hiredate,'yyyy');
其中，deptno为部门号，job为工作的类型(即工种)，hiredate为雇佣的日期，cnt为特定部门，特定工种在特定年份雇佣的员工的总数，sum_sal为特定部门，特定工种，特定年份雇佣的员工的工资的总和。
2> 视图的数据如下：
SQL>select*fromemp_view; DEPTNOJOBHIRECNTSUM_SAL ------------------------------------------- CLERK19801800 ANALYST198113000 ANALYST198713000 CLERK19811950 MANAGER198112850 MANAGER198112450 SALESMAN198145600 MANAGER198112975 PRESIDENT198115000 CLERK198211300 CLERK198711100 rowsselected.
应用场景一：
基本的Pivot转换
例1:
SELECT*FROM
(SELECTdeptno,hiredate,cnt
FROMemp_view
)PIVOT(SUM(cnt)
FORhiredateIN('1980'AS"1980",'1981'AS"1981",
'1982'AS"1982",'1987'AS"1987"))
ORDERBYdeptno;
DEPTNO1980198119821987
--------------------------------------------------
21
122
6
rowsselected.
例2：
SELECT*FROM
(SELECTdeptno,job,cnt
FROMemp_view
)PIVOT(SUM(cnt)
FORjobIN('CLERK','ANALYST','MANAGER','SALESMAN','PRESIDENT'))
ORDERBYdeptno;
DEPTNO'CLERK''ANALYST''MANAGER''SALESMAN''PRESIDENT'
-------------------------------------------------------------
111
221
114
rowsselected.
两例以不同的列进行统计，前者是hiredate，后者是job。
除此之外，前者用了别名，后面没有用别名，两者的显示效果也是不一样的。
应用场景二：
对多列进行Pivot转换
SELECT*FROM
(SELECTdeptno,job,hiredate,cnt
FROMemp_view
)PIVOT(SUM(cnt)
FOR(job,hiredate)IN
(('CLERK','1980')ASclerk_1980,
('CLERK','1981')ASclerk_1981,
('ANALYST','1987')ASanalyst_1987,
('MANAGER','1981')ASmanager_1981
)
)
ORDERbydeptno;
DEPTNOCLERK_1980CLERK_1981ANALYST_1987MANAGER_1981
------------------------------------------------------
1
111
11
rowsselected.
限于篇幅，FOR (job,hiredate) IN语句中没有列出更多组合，只列出了四组，当然，我们可以根据实际场景需要罗列更多的组合。
从本例中可以看出，对两个列进行Pivot转换可从三个维度呈现统计结果。
应用场景三：
用Pivot实现多个聚合
SELECT*FROM
(SELECTdeptno,hiredate,cnt,sum_sal
FROMemp_view
)PIVOT(SUM(cnt)AScnt,
SUM(sum_sal)ASsum_sal
FORhiredateIN('1980','1981','1982','1987'))
ORDERBYdeptno;
DEPTNO'1980'_CNT'1980'_SUM_SAL'1981'_CNT'1981'_SUM_SAL'1982'_CNT'1982'_SUM_SAL'1987'_CNT'1987'_SUM_SAL
----------------------------------------------------------------------------------------------------------
2745011300
18002597524100
69400
rowsselected.
’1981’_CNT指的是1981年雇佣的员工的总数，’1981’_SUM_SAL指的是1981年雇佣员工所开出的工资。
具体到本例中，即1981年10号部门招聘了2位员工，开出的工资合计为7450元，20号部门招聘了2位员工，开出的工资合计为5975元，30号部门招聘了6名员工，开出的工资合计为9400元，依次类推。
既然有pivot将行转换为列，同样也有unpivot操作将聚合后的列转换为行。
UNPIVOT
以上述应用场景三的结果作为源数据进行操作
CREATETABLET1AS
SELECT*FROM
(SELECTdeptno,hiredate,cnt,sum_sal
FROMemp_view
)PIVOT(SUM(cnt)AScnt,
SUM(sum_sal)ASsum_sal
FORhiredateIN('1980'AS"1980",'1981'AS"1981",
'1982'AS"1982",'1987'AS"1987"))
ORDERBYdeptno
表T1的结果为：
SQL>select*fromt1; DEPTNO1980_CNT1980_SUM_SAL1981_CNT1981_SUM_SAL1982_CNT1982_SUM_SAL1987_CNT1987_SUM_SAL -------------------------------------------------------------------------------------------------- 2745011300 18002597524100 69400 rowsselected.
首先进行一维unpivot
SELECTdeptno,DECODE(hiredate,'1980_CNT','1980','1981_CNT','1981','1982_CNT','1982','1987_CNT','1987')AShiredate,cnt
FROMT1
UNPIVOTINCLUDENULLS
(cnt
FORhiredateIN("1980_CNT","1981_CNT","1982_CNT","1987_CNT"));
DEPTNOHIRECNT
------------------------
1980
19812
19821
1987
19801
19812
1982
19872
1980
19816
1982
1987
rowsselected.
输出的结果为不同部门在不同年份的雇佣人数，
注意：上述SQL语句中UNPIVOT后加了INCLUDE NULLS，当然也可以指定为EXCLUDE NULLS，即排除cnt为空的值，如果不指定，则默认为EXCLUDE NULLS。
UNPIVOT后不指定INCLUDE NULLS的输入结果为：
DEPTNOHIRECNT ------------------------ 19812 19821 19801 19812 19872 19816 rowsselected.
下面，我们再进行二维unpivot
SELECTdeptno,hiredate,cnt,sum_sal
FROMT1
UNPIVOT
((cnt,sum_sal)
FORhiredateIN(("1980_CNT","1980_SUM_SAL")AS1980,
("1981_CNT","1981_SUM_SAL")AS1981,
("1982_CNT","1982_SUM_SAL")AS1982,
("1987_CNT","1987_SUM_SAL")AS1987));
DEPTNOHIREDATECNTSUM_SAL
----------------------------------------
198127450
198211300
19801800
198125975
198724100
198169400
rowsselected.
输入结果为T1表列转行的结果。
参考文档：
SQL for Analysis and Reporting
