SQL 变换结果集成一行

SQL 变换结果集成一行，你想把若干行数据重新组合成一行新数据，原始数据集的每一行变换后会作为新数据的一列出现。例如，下面的结果集展示了每个部门的员工人数。

SQL 变换结果集成一行 问题描述

你想把若干行数据重新组合成一行新数据，原始数据集的每一行变换后会作为新数据的一列出现。例如，下面的结果集展示了每个部门的员工人数。

DEPTNO        CNT
------ ----------
    10          3
    20          5
    30          6

你希望把上述结果集重新格式化成下面的输出结果。

DEPTNO_10  DEPTNO_20  DEPTNO_30
--------- ---------- ----------
        3          5          6

SQL 变换结果集成一行 解决方案

使用 CASE 表达式和聚合函数 SUM 实现结果集变换。

1 select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
2        sum(case when deptno=20 then 1 else 0 end) as deptno_20,
3        sum(case when deptno=30 then 1 else 0 end) as deptno_30
4 from emp

SQL 变换结果集成一行 扩展知识

这是一个非常好的结果集变换入门实例。它的做法其实很简单：对于每一行的原始数据，使用 CASE 表达式把行变换成列。然后，由于本实例要合计每个部门的员工人数，因此需要调用聚合函数 SUM 计算出每个 DEPTNO 出现的次数。如果你还是不太理解以上解决方案的工作原理，那么不妨先执行下面的查询，该查询调用聚合函数 SUM，并加上了 DEPTNO 以增强可读性。

select deptno,
       case when deptno=10 then 1 else 0 end as deptno_10,
       case when deptno=20 then 1 else 0 end as deptno_20,
       case when deptno=30 then 1 else 0 end as deptno_30
  from emp
 order by 1
　
DEPTNO  DEPTNO_10  DEPTNO_20  DEPTNO_30
------ ---------- ---------- ----------
    10          1          0          0
    10          1          0          0
    10          1          0          0
    20          0          1          0
    20          0          1          0
    20          0          1          0
    20          0          1          0
    20          0          1          0
    30          0          0          1
    30          0          0          1
    30          0          0          1
    30          0          0          1
    30          0          0          1
    30          0          0          1

我们可以把每一个 CASE 表达式的结果值想象成一个标志位，它表示每个 DEPTNO 属于哪一行。至此，“把行变成列”的操作已经完成。下一步只要合计 DEPTNO_10、DEPTNO_20 和 DEPTNO_30 的值，并按照 DEPTNO 分组即可，结果集显示如下。

select deptno,
       sum(case when deptno=10 then 1 else 0 end) as deptno_10,
       sum(case when deptno=20 then 1 else 0 end) as deptno_20,
       sum(case when deptno=30 then 1 else 0 end) as deptno_30
  from emp
 group by deptno
　
DEPTNO  DEPTNO_10  DEPTNO_20  DEPTNO_30
------ ---------- ---------- ----------
    10          3          0          0
    20          0          5          0
    30          0          0          6

仔细观察上述结果集的话，可以发现上述输出结果在逻辑上是正确的。例如，DEPTNO 等于 10 的部门对应的行里面，DEPTNO_10 的值为 3，而其他列都是 0。因为我们的目标是只返回一行数据，最后一步就是要舍弃 DEPTNO 和 GROUP BY，只针对 CASE 表达式的结果执行 SUM 函数即可。

select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
       sum(case when deptno=20 then 1 else 0 end) as deptno_20,
       sum(case when deptno=30 then 1 else 0 end) as deptno_30
  from emp
　
DEPTNO_10  DEPTNO_20  DEPTNO_30
--------- ---------- ----------
        3          5          6

对于这一类问题，还有一种可能的做法。

select max(case when deptno=10 then empcount else null end) as deptno_10,
       max(case when deptno=20 then empcount else null end) as deptno_20,
       max(case when deptno=30 then empcount else null end) as deptno_30
  from (
select deptno, count(*) as empcount
  from emp
 group by deptno
       ) x

上述方法用内嵌视图生成每个部门的员工总人数。主查询里的 CASE 表达式把行转换成列，将得到下面的结果。

DEPTNO_10  DEPTNO_20  DEPTNO_30
--------- ---------- ----------
        3       Null       Null
     Null          5       Null
     Null       Null          6

然后，调用 MAX 函数把几列合并为一行。

DEPTNO_10  DEPTNO_20  DEPTNO_30
--------- ---------- ----------
        3          5          6