极客教程SQL 使用SQL Server的PIVOT操作符创建交叉报表，你想创建一个交叉报表，以实现把行形式的结果集转换为列形式。你已经掌握了传统的做法，但这次希望尝试一些不同的技巧。尤其是你希望不使用 CASE 表达式或连接操作就能返回如下所示的结果集。
SQL 使用SQL Server的PIVOT操作符创建交叉报表 问题描述
你想创建一个交叉报表，以实现把行形式的结果集转换为列形式。你已经掌握了传统的做法，但这次希望尝试一些不同的技巧。尤其是你希望不使用 CASE 表达式或连接操作就能返回如下所示的结果集。
DEPT_10 DEPT_20 DEPT_30 DEPT_40
------- ---------- ---------- ----------
3 5 6 0
SQL 使用SQL Server的PIVOT操作符创建交叉报表 解决方案
使用 PIVOT 操作符生成要求的结果集，而不使用 CASE 表达式或额外的连接操作。
1 select [10] as dept_10,
2 [20] as dept_20,
3 [30] as dept_30,
4 [40] as dept_40
5 from (select deptno, empno from emp) driver
6 pivot (
7 count(driver.empno)
8 for driver.deptno in ( [10],[20],[30],[40] )
9 ) as empPivot
SQL 使用SQL Server的PIVOT操作符创建交叉报表 扩展知识
最初看到 PIVOT 操作符可能会觉得陌生，其实在上述解决方案中，它执行的操作在技术上等价于我们所熟知的形式变换查询，如下所示。
select sum(case deptno when 10 then 1 else 0 end) as dept_10,
sum(case deptno when 20 then 1 else 0 end) as dept_20,
sum(case deptno when 30 then 1 else 0 end) as dept_30,
sum(case deptno when 40 then 1 else 0 end) as dept_40
from emp
DEPT_10 DEPT_20 DEPT_30 DEPT_40
------- ---------- ---------- ----------
3 5 6 0
在对 PIVOT 操作有了基本的了解后，现在我们把它拆解开来，看看它究竟做了些什么。上述解决方案中的第 5 行展示了内嵌视图 DRIVER。
from (select deptno, empno from emp) driver
我选择使用别名 driver，是因为该内嵌视图［即“表表达式”（table expression）］中的数据会直接流入 PIVOT 操作。PIVOT 操作符通过评估第 8 行的 FOR 列表中的项目把行转换为列，如下所示。
for driver.deptno in ( [10],[20],[30],[40] )
执行过程大致如下。
(1) 如果 DEPTNO 等于 10，就针对相关的行执行预先定义好的聚合操作（COUNT(DRIVER.EMPNO)）。
(2) 针对 DEPTNO 等于 20、30 和 40 的行重复同样的操作。
第 8 行方括号里的项目不仅能够为聚合操作提供值，同时，这些项目也变成了结果集里的列名（不带方括号）。上述解决方案的 SELECT 子句也引用了 FOR 列表里的那些项目，并为它们分别指定了别名。如果不为 FOR 列表里的项目指定别名，则那些项目就会变成列名，但是会去掉方括号。
还有一个非常有趣之处，由于 DRIVER 只是一个普通的内嵌视图，因此可以使用更复杂的 SQL。假设我们希望修改结果集，把实际的部门名称作为结果集的列名。下面列出了 DEPT 表的数据。
select * from dept
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
我们希望借助 PIVOT 返回如下所示的结果集。
ACCOUNTING RESEARCH SALES OPERATIONS
---------- ---------- ---------- ----------
3 5 6 0
内嵌视图 DRIVER 事实上能够接受任何有效的表表达式，因此可以先把 EMP 表和 DEPT 表连接起来，然后使用 PIVOT 逐一评估其查询结果。下面的查询将返回上述要求的结果集。
select [ACCOUNTING] as ACCOUNTING,
[SALES] as SALES,
[RESEARCH] as RESEARCH,
[OPERATIONS] as OPERATIONS
from (
select d.dname, e.empno
from emp e,dept d
where e.deptno=d.deptno
) driver
pivot (
count(driver.empno)
for driver.dname in ([ACCOUNTING],[SALES],[RESEARCH],[OPERATIONS])
) as empPivot
如上所述，PIVOT 提供了一种有趣的转换结果集的办法。如果你以前习惯使用传统的转换方法，现在不妨将其作为另一种选择放入你的工具箱。