PostgreSQL中实现列转行：将分组列转置为值并汇总

你需要将现有的宽表结构（日期、部门、蓝队、红队、绿队）转换为包含日期、部门、team_type、汇总值的长表结构，以下提供两种可行方案：

方案一：基于现有宽表结果转换 #

如果已经通过现有查询得到了目标宽表，可使用UNION ALL将各队伍列拆分为独立行：

SELECT 日期, 部门, '蓝队' AS team_type, 蓝队 AS 汇总值
FROM 你的宽表结果表 -- 替换为实际的宽表名称或CTE
UNION ALL
SELECT 日期, 部门, '红队' AS team_type, 红队 AS 汇总值
FROM 你的宽表结果表
UNION ALL
SELECT 日期, 部门, '绿队' AS team_type, 绿队 AS 汇总值
FROM 你的宽表结果表
ORDER BY 日期, 部门, team_type;

方案二：直接从原始表生成目标结构（更高效） #

跳过中间宽表步骤，直接从results表聚合并转换结构，避免冗余计算：

SELECT
    date AS 日期,
    depart AS 部门,
    CASE team_name
        WHEN 'blue' THEN '蓝队'
        WHEN 'red' THEN '红队'
        WHEN 'green' THEN '绿队'
    END AS team_type,
    SUM(scores) AS 汇总值
FROM results
GROUP BY date, depart, team_name
ORDER BY date, depart, team_type;

结合现有CTE的优化版本 #

如果需要保留现有CTE逻辑，可将其与UNION ALL结合：

WITH res AS (
    SELECT
        date,
        depart,
        SUM(CASE WHEN team_name = 'blue' THEN scores END) AS 蓝队,
        SUM(CASE WHEN team_name = 'red' THEN scores END) AS 红队,
        SUM(CASE WHEN team_name = 'green' THEN scores END) AS 绿队
    FROM results
    GROUP BY date, depart
)
SELECT date AS 日期, depart AS 部门, '蓝队' AS team_type, 蓝队 AS 汇总值 FROM res
UNION ALL
SELECT date AS 日期, depart AS 部门, '红队' AS team_type, 红队 AS 汇总值 FROM res
UNION ALL
SELECT date AS 日期, depart AS 部门, '绿队' AS team_type, 绿队 AS 汇总值 FROM res
ORDER BY 日期, 部门, team_type;

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