如何转置SUM(CASE WHEN...)表达式输出的SQL统计结果？

我现在用SUM(CASE WHEN...)对Table1的Age字段按年龄段分类并统计数量，当前SQL语句如下：

SELECT SUM(CASE WHEN Age BETWEEN '0' AND '11' THEN 1 ELSE 0 END) AS "0-11", 
       SUM(CASE WHEN Age BETWEEN '12' AND '17' THEN 1 ELSE 0 END) AS "12-17",
       SUM(CASE WHEN Age BETWEEN '18' AND '24' THEN 1 ELSE 0 END) AS "18-24",
       SUM(CASE WHEN Age BETWEEN '25' AND '64' THEN 1 ELSE 0 END) AS "25-64",
       SUM(CASE WHEN Age >= '65' THEN 1 ELSE 0 END) AS "65+"
   FROM Table1

该语句输出的是列形式的统计结果：

我需要把结果转置为以下行形式的结构：

我尝试用PIVOT但不知道怎么填写columnname，尝试的代码如下：

SELECT SUM(CASE WHEN Age BETWEEN '0' AND '11' THEN 1 ELSE 0 END) AS "0-11", 
       SUM(CASE WHEN Age BETWEEN '12' AND '17' THEN 1 ELSE 0 END) AS "12-17",
       SUM(CASE WHEN Age BETWEEN '18' AND '24' THEN 1 ELSE 0 END) AS "18-24",
       SUM(CASE WHEN Age BETWEEN '25' AND '64' THEN 1 ELSE 0 END) AS "25-64",
       SUM(CASE WHEN Age >= '65' THEN 1 ELSE 0 END) AS "65+"
   FROM Table1
pivot
    (
        max(value)
        FOR columnname IN ("0-11", "12-17", "18-24", "25-64", "65+")
    ) 

解决方案 #

你这里搞反了操作逻辑：PIVOT是把行转成列，而你需要的是列转行，应该用UNION ALL或者先分组年龄段再聚合，两种可行方法如下：

方法1：用UNION拆分每个年龄段的统计

SELECT '0-11' AS Age, SUM(CASE WHEN Age BETWEEN '0' AND '11' THEN 1 ELSE 0 END) AS Count FROM Table1
UNION ALL
SELECT '12-17' AS Age, SUM(CASE WHEN Age BETWEEN '12' AND '17' THEN 1 ELSE 0 END) AS Count FROM Table1
UNION ALL
SELECT '18-24' AS Age, SUM(CASE WHEN Age BETWEEN '18' AND '24' THEN 1 ELSE 0 END) AS Count FROM Table1
UNION ALL
SELECT '25-64' AS Age, SUM(CASE WHEN Age BETWEEN '25' AND '64' THEN 1 ELSE 0 END) AS Count FROM Table1
UNION ALL
SELECT '65+' AS Age, SUM(CASE WHEN Age >= '65' THEN 1 ELSE 0 END) AS Count FROM Table1;

方法2：先分组年龄段再聚合（更高效）

SELECT
    CASE
        WHEN Age BETWEEN '0' AND '11' THEN '0-11'
        WHEN Age BETWEEN '12' AND '17' THEN '12-17'
        WHEN Age BETWEEN '18' AND '24' THEN '18-24'
        WHEN Age BETWEEN '25' AND '64' THEN '25-64'
        WHEN Age >= '65' THEN '65+'
    END AS Age,
    COUNT(*) AS Count
FROM Table1
GROUP BY
    CASE
        WHEN Age BETWEEN '0' AND '11' THEN '0-11'
        WHEN Age BETWEEN '12' AND '17' THEN '12-17'
        WHEN Age BETWEEN '18' AND '24' THEN '18-24'
        WHEN Age BETWEEN '25' AND '64' THEN '25-64'
        WHEN Age >= '65' THEN '65+'
    END
ORDER BY Age;

如果你的SQL支持GROUP BY引用别名（比如MySQL 8.0+、PostgreSQL等），可以简化为：

SELECT
    CASE
        WHEN Age BETWEEN '0' AND '11' THEN '0-11'
        WHEN Age BETWEEN '12' AND '17' THEN '12-17'
        WHEN Age BETWEEN '18' AND '24' THEN '18-24'
        WHEN Age BETWEEN '25' AND '64' THEN '25-64'
        WHEN Age >= '65' THEN '65+'
    END AS Age,
    COUNT(*) AS Count
FROM Table1
GROUP BY Age
ORDER BY Age;

内容的提问来源于stack exchange，提问作者Jung