SQL Server多动态透视（Multi Dynamic Pivot）列重命名问题

针对你遇到的按id_group分组，将多行的id、timestamp、outcome按operation展平为单行并自定义列名的需求，以下是可行的动态SQL解决方案，同时解释你之前返回全NULL的原因：

核心思路 #

多列透视无法直接通过单一PIVOT实现（PIVOT仅支持单个聚合列），因此采用CASE表达式+聚合函数的方式，结合行号区分同组内重复的operation，再动态拼接自定义列名。

步骤与代码实现 #

1. 为同组内的重复operation添加序号 #

首先，给每个id_group下的相同operation添加行号，用于生成如Mariage 1、Mariage 2这类区分性的列名：

SELECT 
    id_group,
    id,
    timestamp,
    outcome,
    operation,
    -- 按id_group和operation分组，按timestamp排序生成序号
    ROW_NUMBER() OVER(PARTITION BY id_group, operation ORDER BY timestamp) AS op_seq
FROM [test].[dbo].[operations_assy]

2. 动态拼接自定义列名 #

通过STRING_AGG（SQL Server 2017+支持）拼接所有需要的自定义列，每个operation+序号对应三列（id、timestamp、outcome）：

DECLARE @cols NVARCHAR(MAX)

SELECT @cols = STRING_AGG(
    CONCAT(
        'MAX(CASE WHEN operation = ''', operation, ''' AND op_seq = ', op_seq, ' THEN id END) AS ''', operation, ' ', op_seq, ' id'',',
        'MAX(CASE WHEN operation = ''', operation, ''' AND op_seq = ', op_seq, ' THEN timestamp END) AS ''', operation, ' ', op_seq, ' timestamp'',',
        'MAX(CASE WHEN operation = ''', operation, ''' AND op_seq = ', op_seq, ' THEN outcome END) AS ''', operation, ' ', op_seq, ' outcome'''
    ),
    ','
)
FROM (
    -- 去重获取所有唯一的operation+序号组合
    SELECT DISTINCT operation, op_seq
    FROM (
        SELECT 
            operation,
            ROW_NUMBER() OVER(PARTITION BY id_group, operation ORDER BY timestamp) AS op_seq
        FROM [test].[dbo].[operations_assy]
    ) t
) t2

3. 构建并执行动态SQL #

将拼接好的列插入到主查询中，按id_group分组聚合：

DECLARE @sql NVARCHAR(MAX)

SET @sql = CONCAT('
SELECT 
    id_group,', @cols, '
FROM (
    SELECT 
        id_group,
        id,
        timestamp,
        outcome,
        operation,
        ROW_NUMBER() OVER(PARTITION BY id_group, operation ORDER BY timestamp) AS op_seq
    FROM [test].[dbo].[operations_assy]
) t
GROUP BY id_group
ORDER BY id_group
')

-- 执行动态SQL
EXEC sp_executesql @sql

你之前返回全NULL的原因分析 #

1. 单一PIVOT的局限性：若你尝试用PIVOT同时处理多列（如timestamp和outcome），会因PIVOT仅支持单个聚合列导致匹配失败，返回NULL。
2. 列名与条件不匹配：自定义列名时，若CASE表达式的条件（如operation匹配、序号匹配）与子查询中的字段逻辑不一致，会导致无法命中数据，返回NULL。
3. 未区分重复operation：同个id_group下存在相同operation时，未添加行号区分，导致聚合时无法正确映射到对应的行。

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