如何将含聚合函数的SQL查询列转置为行？

你当前的问题是：通过CASE+SUM生成带别名的聚合列后，无法直接用UNPIVOT转置为行——UNPIVOT只能识别基础列名，既不能直接使用聚合表达式，也无法引用SELECT子句里的别名。以下是几种简便的解决方法：

方法1：用子查询/CTE包装聚合结果后再UNPIVOT #

先把聚合后的结果放到子查询或CTE中，让UNPIVOT能识别到明确的列名：

WITH aggregated_data AS (
    SELECT
        SUM(CASE WHEN reknr = 4103 THEN bdr_val END) AS "4103",
        SUM(CASE WHEN reknr = 4303 THEN bdr_val END) AS "4303",
        SUM(CASE WHEN reknr = 4309 THEN bdr_val END) AS "4309"
        -- 按需添加其他列
    FROM gbkmut
    WHERE 
        datum BETWEEN '20220701' AND '20220731'
        AND transtype IN ('N', 'C', 'P')
        -- 其他过滤条件
)
SELECT reknr, total_value
FROM aggregated_data
UNPIVOT (
    total_value FOR reknr IN ("4103", "4303", "4309") -- 列名需与CTE中的别名完全一致
) AS unpivoted_data;

方法2：用VALUES子句手动展开（列数不多时更灵活） #

无需依赖UNPIVOT，直接通过VALUES子句把聚合列拆成行：

SELECT
    unpivoted.reknr,
    unpivoted.total_value
FROM (
    SELECT
        SUM(CASE WHEN reknr = 4103 THEN bdr_val END) AS "4103",
        SUM(CASE WHEN reknr = 4303 THEN bdr_val END) AS "4303",
        SUM(CASE WHEN reknr = 4309 THEN bdr_val END) AS "4309"
        -- 按需添加其他列
    FROM gbkmut
    WHERE 
        datum BETWEEN '20220701' AND '20220731'
        AND transtype IN ('N', 'C', 'P')
        -- 其他过滤条件
) AS aggregated
CROSS APPLY (
    VALUES
        ('4103', "4103"),
        ('4303', "4303"),
        ('4309', "4309")
        -- 对应添加其他列的别名和值
) AS unpivoted(reknr, total_value);

注：如果使用PostgreSQL，将CROSS APPLY替换为CROSS JOIN LATERAL即可。

方法3：先分组聚合直接得到行结果（性能最优） #

跳过「先转列再转行」的冗余步骤，直接按reknr分组聚合，一步得到目标结果：

SELECT
    CAST(reknr AS VARCHAR(4)) AS reknr, -- 转成字符串与原别名格式匹配
    SUM(bdr_val) AS total_value
FROM gbkmut
WHERE 
    datum BETWEEN '20220701' AND '20220731'
    AND transtype IN ('N', 'C', 'P')
    AND reknr IN (4103, 4303, 4309) -- 仅筛选需要的reknr值
    -- 其他过滤条件
GROUP BY reknr;

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