如何使用SQL GROUP BY school_name实现行转列生成指定报表？

源数据表 #

目标输出结构 #

实现SQL语句 #

通过条件聚合可以实现行转列的需求，具体SQL如下：

SELECT
    school_name,
    MAX(CASE WHEN class = 1 AND medium = 'english' THEN total END) AS class1eng,
    MAX(CASE WHEN class = 1 AND medium = 'french' THEN total END) AS class1french,
    MAX(CASE WHEN class = 2 AND medium = 'english' THEN total END) AS class2eng,
    MAX(CASE WHEN class = 2 AND medium = 'french' THEN total END) AS class2french,
    MAX(CASE WHEN class = 3 AND medium = 'english' THEN total END) AS class3english,
    MAX(CASE WHEN class = 3 AND medium = 'french' THEN total END) AS class3french
FROM
    your_table_name
GROUP BY
    school_name;

说明 #

• 用CASE WHEN匹配每行的class和medium组合，符合条件时返回对应total值，否则返回NULL
• 借助MAX()聚合函数提取每个school_name下对应组合的有效total值（分组后每个组合仅存在一个非NULL值，MAX可直接获取该值）
• 替换语句中的your_table_name为实际数据表名称

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