如何将多年度参会者注册表数据转换为按年度列展示参会者及对应注册码的汇总表

嘿，我看了你的需求，刚好可以用条件聚合的思路来完美解决，完全能实现你想要的那种宽表格式。先明确你的核心诉求：把分散在5个年度表里的参会数据，按「姓名+邮箱」合并成唯一参会者行，每个年度作为一列展示对应注册码，没参会的年份留空。

先回顾下你期望的输出效果：

解决思路 #

核心分两步走：

• 第一步：统一整合所有年度数据：用UNION ALL把5个年度的注册表合并，同时给每条记录打上年度标签，这样我们能追踪每条注册记录属于哪一年。
• 第二步：条件聚合生成宽表：以「first_name + last_name + email」作为分组依据（这三个字段唯一标识一个参会者，完全符合你定义的规则），然后针对每个年度，用CASE语句配合聚合函数提取对应注册码。

完整SQL代码 #

WITH all_regs AS (
    -- 2022年度数据，添加年度标记
    SELECT 
        first_name,
        last_name,
        email,
        reg_code,
        2022 AS attend_year
    FROM [2022_show].[dbo].[regs]
    
    UNION ALL
    
    -- 2023年度数据
    SELECT 
        first_name,
        last_name,
        email,
        reg_code,
        2023 AS attend_year
    FROM [2023_show].[dbo].[regs]
    
    UNION ALL
    
    -- 2024年度数据
    SELECT 
        first_name,
        last_name,
        email,
        reg_code,
        2024 AS attend_year
    FROM [2024_show].[dbo].[regs]
    
    UNION ALL
    
    -- 2025年度数据
    SELECT 
        first_name,
        last_name,
        email,
        reg_code,
        2025 AS attend_year
    FROM [2025_show].[dbo].[regs]
    
    UNION ALL
    
    -- 2026年度数据
    SELECT 
        first_name,
        last_name,
        email,
        reg_code,
        2026 AS attend_year
    FROM [2026_show].[dbo].[regs]
)
SELECT 
    -- 拼接姓名为Name列
    CONCAT(first_name, ' ', last_name) AS Name,
    email AS Email,
    -- 提取2022年的注册码，无数据则为空
    MAX(CASE WHEN attend_year = 2022 THEN reg_code END) AS [2022],
    MAX(CASE WHEN attend_year = 2023 THEN reg_code END) AS [2023],
    MAX(CASE WHEN attend_year = 2024 THEN reg_code END) AS [2024],
    MAX(CASE WHEN attend_year = 2025 THEN reg_code END) AS [2025],
    MAX(CASE WHEN attend_year = 2026 THEN reg_code END) AS [2026]
FROM all_regs
-- 按唯一参会者标识分组
GROUP BY first_name, last_name, email
-- 可选：按姓名排序，让结果更整洁
ORDER BY last_name, first_name;

关键细节解释 #

1. CTE合并数据部分：

   • 用UNION ALL而非UNION是因为前者不需要自动去重，效率更高——毕竟你每个参会者每年只会有一条注册记录，不需要额外去重操作。
   • 每个表的查询都添加了attend_year字段，这是后续按年度提取数据的关键标记。

2. 条件聚合生成列部分：

   • MAX(CASE ...)的作用是：如果该参会者对应年度有注册记录，就返回reg_code，否则返回NULL（对应表格里的空单元格）。用MAX是因为分组后同一个人同一年只会有一条有效记录，聚合函数会自动忽略NULL值，提取出唯一的非空注册码。
   • GROUP BY first_name, last_name, email完美匹配你对“同一个参会者”的定义：只要这三个字段有一个不同，就会被当成新的分组，生成新的行，完全符合你说的「换邮箱就算新用户」的要求。

效果验证 #

这个SQL生成的结果和你给出的示例表格完全一致：

• Joe Anderson每年的注册码会精准对应到2022-2026的列；
• Jane Johnson只有2025年的BG，其他列留空；
• Fred Jones在2023、2025、2026年的F会正确填充；
• John Smith的2022-2024年的CR正常展示，2025-2026年为空。

备注：内容来源于stack exchange，提问作者Laurence MacNeill