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

Python教程

Java教程

Web教程

数据库教程

图形图像教程

大数据教程

开发工具教程

计算机教程