如何在SQL中实现宽表转换或使用动态版LEAD函数？

原始长表数据：

Policy Number Benefit Code Transaction Code
1             A            2
1             B            1
2             A            3
3             A            2
1             C            2

期望转换后的宽表形式：

PN BC 1 TC 1 BC 2 TC 2 BC 3 TC 3
1  A    2    B    1    C    2
2  A    3    NULL NULL NULL NULL
3  A    2    NULL NULL NULL NULL

已知R语言的tidyverse包可实现长表转宽表，也了解SQL中的LEAD函数，但每个保单的福利码和交易码数量不固定，因此有以下问题：

1. 如何在SQL中实现上述宽表转换？
2. 除宽表转换外，SQL中是否存在更动态的LEAD函数，可将分组（如保单号）的后续行转换为新列？

1. SQL实现动态长转宽 #

由于每个保单的行数不固定，静态SQL无法适配所有情况，必须使用动态SQL生成对应列。以下是通用实现思路（以MySQL为例，不同数据库语法略有差异）：

步骤1：确定分组内的最大行数 #

先统计每个保单的福利码数量最大值，以此确定需要生成多少组BC n和TC n列：

SELECT MAX(row_num) AS max_cols
FROM (
    SELECT `Policy Number`, 
           ROW_NUMBER() OVER(PARTITION BY `Policy Number` ORDER BY `Benefit Code`) AS row_num
    FROM your_table
) t;

步骤2：生成并执行动态SQL语句 #

根据最大行数，拼接出用于转宽表的字段逻辑，最终执行动态生成的SQL：

-- 获取最大列数
SET @max_cols = (SELECT MAX(row_num) FROM (SELECT `Policy Number`, ROW_NUMBER() OVER(PARTITION BY `Policy Number` ORDER BY `Benefit Code`) AS row_num FROM your_table) t);

-- 初始化SQL字符串
SET @sql = '';
SET @i = 1;

-- 循环拼接列逻辑
WHILE @i <= @max_cols DO
    SET @sql = CONCAT(@sql, 
                      ', MAX(IF(row_num = ', @i, ', `Benefit Code`, NULL)) AS `BC ', @i, '`',
                      ', MAX(IF(row_num = ', @i, ', `Transaction Code`, NULL)) AS `TC ', @i, '`');
    SET @i = @i + 1;
END WHILE;

-- 拼接完整SQL并执行
SET @sql = CONCAT('SELECT `Policy Number` AS PN ', @sql, ' FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY `Policy Number` ORDER BY `Benefit Code`) AS row_num FROM your_table) t GROUP BY `Policy Number`');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

如果使用SQL Server、PostgreSQL等数据库，动态SQL的语法会有差异（比如SQL Server用STRING_AGG或游标拼接，PostgreSQL用EXECUTE结合字符串拼接），但核心逻辑一致：先给分组内的行编号，再根据编号将每行的字段映射为新列。

2. 关于动态LEAD函数的问题 #

SQL中没有原生的"动态LEAD函数"可以自动将分组内的后续行批量转为新列。LEAD函数仅能获取固定偏移量的行值（比如LEAD(col, 1)取分组内的下一行），无法根据分组内的行数动态生成多个列。

要实现这种动态转列需求，本质还是要依赖动态SQL的思路：先为分组内的每行分配序号，再根据序号将每行的字段转为对应列。

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