SQL中使用CASE语句实现表透视报错的解决方法

问题背景 #

现有存储疫苗接种记录的表tbl，样例数据如下：

ID  service_date vax_type   dose   age   gender
A   2020-01-04   pfizer     dose1  30    M
A   2020-02-04   pfizer     dose2  30    M
B   2020-02-10   pfizer     dose1  40    M   
B   2020-03-04   pfizer     dose2  40    M
C   2020-03-14   moderna    dose1  50    F
C   2020-04-01   moderna    dose2  50    F

需要将同一用户的不同剂次接种日期透视到同一行，目标结果结构：

ID  vax_type  dose1      dose2       age   gender
A   pfizer    2020-01-04 2020-02-04  30    M
B   pfizer    2020-02-10 2020-03-04  40    M
C   moderna   2020-03-14 2020-04-01  50    F

最初编写的SQL存在两处问题，导致报错或结果不符合预期：

• 语法错误：gender字段末尾缺少逗号，直接衔接后续CASE语句，触发语法报错
• 逻辑错误：未添加分组聚合逻辑，查询会返回原始6行数据，每行仅填充对应剂次的日期，另一列为NULL，无法实现行合并。

实现方案 #

方案1：通用CASE+聚合写法（全数据库兼容） #

这是适配所有SQL数据库的通用写法，核心逻辑是按非透视维度分组，通过聚合函数提取对应剂次的日期值：

SELECT 
    ID, 
    vax_type, 
    age, 
    gender,
    MAX(CASE WHEN dose = 'dose1' THEN service_date END) AS dose1,
    MAX(CASE WHEN dose = 'dose2' THEN service_date END) AS dose2
FROM tbl
GROUP BY ID, vax_type, age, gender;

说明：CASE表达式未指定ELSE分支时默认返回NULL，MAX聚合会自动忽略NULL值，同一分组下每个剂次对应唯一的接种日期，用MAX/MIN都能得到正确结果。

方案2：原生PIVOT算子写法（适配支持PIVOT的数据库） #

SQL Server、Oracle、Spark SQL等数据库提供原生PIVOT语法，写法更简洁直观：

-- SQL Server 示例
SELECT ID, vax_type, age, gender, dose1, dose2
FROM tbl
PIVOT (
    MAX(service_date)
    FOR dose IN (dose1, dose2)
) AS pvt_result;

注意事项 #

• 分组字段需要覆盖所有非透视的固定属性字段，本例中同一ID对应的疫苗类型、年龄、性别均为固定值，加入GROUP BY不会改变分组粒度，也不会出现数据错位
• 聚合函数不可省略，否则无法将多行同组数据折叠为单行
• 注意SELECT子句中字段之间的逗号分隔，避免低级语法错误

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