SQL 使用SQL Server的UNPIVOT操作符逆向转换交叉报表|极客教程

SQL 使用SQL Server的UNPIVOT操作符逆向转换交叉报表，你有一个格式良好的结果集，即“宽表”（fat table），你想针对该结果集进行逆向转换。例如，你希望把 1 行 4 列的结果集变成 4 行 2 列。

SQL 使用SQL Server的UNPIVOT操作符逆向转换交叉报表问题描述

你有一个格式良好的结果集，即“宽表”（fat table），你想针对该结果集进行逆向转换。例如，你希望把 1 行 4 列的结果集变成 4 行 2 列。对于 14.1 节中的结果集：

ACCOUNTING   RESEARCH      SALES OPERATIONS
---------- ---------- ---------- ----------
         3          5          6          0

你想把它转换成下面这样。

DNAME                 CNT
-------------- ----------
ACCOUNTING              3
RESEARCH                5
SALES                   6
OPERATIONS              0

SQL 使用SQL Server的UNPIVOT操作符逆向转换交叉报表解决方案

你应该猜到 SQL Server 除了支持 PIVOT 外，也同时支持 UNPIVOT。为了实现结果的逆向转换，只要把前一个实例的查询作为 driver，并把剩下的工作交给 UNPIVOT 操作符即可。我们只需要指定列名。

 1  select DNAME, CNT
 2    from (
 3      select [ACCOUNTING] as ACCOUNTING,
 4             [SALES]      as SALES,
 5             [RESEARCH]   as RESEARCH,
 6             [OPERATIONS] as OPERATIONS
 7        from (
 8                select d.dname, e.empno
 9                  from emp e,dept d
10                 where e.deptno=d.deptno
11
12             ) driver
13       pivot (
14         count(driver.empno)
15         for driver.dname in ([ACCOUNTING],[SALES],[RESEARCH],[OPERATIONS])
16       ) as empPivot
17  ) new_driver
18  unpivot (cnt for dname in (ACCOUNTING,SALES,RESEARCH,OPERATIONS)
19  ) as un_pivot

但愿你在此之前已经阅读过前一个实例，因为内嵌视图 NEW_DRIVER 直接使用了前一个实例的代码。（如果你不理解该代码，请先查阅前一个实例。）由于你已经理解了第 3 ～ 16 行的代码，唯一陌生的语法就是第 18 行的 UNPIVOT。
UNPIVOT 命令会查看来自 NEW_DRIVER 的结果集，并评估每一行和每一列。例如，UNPIVOT 操作符会评估 NEW_DRIVER 返回的列名。如果等于 ACCOUNTING，就把列名 ACCOUNTING 转换为一个行值（放置于 DNAME 列）。它也会从 NEW_DRIVER 中提取出 ACCOUNTING 的值（该值等于 3），并将其转换为 ACCOUNTING 行的一部分（放置于 CNT 列）。UNPIVOT 会针对 FOR 列表中指定的每个项目执行类似的操作，并把每一项都转换为一行记录。
新的结果集变得“窄”了许多，只有 DNAME 列和 CNT 列，并且有 4 行数据。

select DNAME, CNT
  from (
    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
) new_driver
unpivot (cnt for dname in (ACCOUNTING,SALES,RESEARCH,OPERATIONS)
) as un_pivot
　
DNAME                 CNT
-------------- ----------
ACCOUNTING              3
RESEARCH                5
SALES                   6
OPERATIONS              0