SQL分块对表执行pivot：按ID分组行转列是否需用大量CASE WHEN

结论 #

不需要手动编写大量CASE WHEN语句，可以根据你使用的数据库类型选择更简洁的实现方式：

• 支持原生PIVOT语法的数据库，直接调用原生关键字即可完成转换
• 不支持原生PIVOT的数据库，可通过动态SQL自动生成转换逻辑，无需手动逐个规则编码

具体实现参考 #

1. 原生PIVOT语法实现（适用于SQL Server、Oracle、PostgreSQL 15+、Spark SQL等支持原生PIVOT的数据库，以SQL Server为例） #

SELECT 
  Chart_ID,
  'R1' AS R1, [R1] AS Action1, 
  'R2' AS R2, [R2] AS Action2,
  'R3' AS R3, [R3] AS Action3,
  'R4' AS R4, [R4] AS Action4
FROM (
  SELECT Chart_ID, Rule, Action 
  FROM 原始表
) AS SourceTable
PIVOT (
  MAX(Action)
  FOR Rule IN ([R1], [R2], [R3], [R4])
) AS PivotTable

2. 动态SQL实现（兼容无原生PIVOT的场景，以MySQL为例） #

无需手动枚举所有Rule，代码会自动读取现有规则生成转换逻辑，后续新增R4、R5等规则时也无需修改代码，可自动适配新增的规则列：

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
  CONCAT(
    'MAX(CASE WHEN Rule = ''', Rule, ''' THEN ''', Rule, ''' END) AS ', Rule, ', ',
    'MAX(CASE WHEN Rule = ''', Rule, ''' THEN Action END) AS Action', RIGHT(Rule, 1)
  )
) INTO @sql
FROM 原始表;

SET @sql = CONCAT('SELECT Chart_ID, ', @sql, ' FROM 原始表 GROUP BY Chart_ID');

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

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