SQL 使用SQL Server的PIVOT操作符创建交叉报表|极客教程

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 提供了一种有趣的转换结果集的办法。如果你以前习惯使用传统的转换方法，现在不妨将其作为另一种选择放入你的工具箱。