如何在SQL Pivot中插入关联列（如训练时长列）

要实现每个训练的分数与对应时长列相邻的动态报表，放弃使用PIVOT运算符，改用动态生成MAX(CASE...)表达式的方式，能完全控制列的顺序，适配任意数量的训练项。

完整SQL代码（SQL Server 2017+） #

DECLARE @DynamicColumns NVARCHAR(MAX)
DECLARE @FinalSQL NVARCHAR(MAX)

-- 动态生成每个训练对应的分数列+时长列表达式，保证列相邻
SELECT @DynamicColumns = STRING_AGG(
    CONCAT(
        'MAX(CASE WHEN A.NM_TRAINING = ''', NM_TRAINING, ''' THEN B.SCORE END) AS [', NM_TRAINING, '], ',
        'MAX(CASE WHEN A.NM_TRAINING = ''', NM_TRAINING, ''' THEN B.TIME END) AS [Time_', NM_TRAINING, ']'
    ),
    ', '
)
FROM TBL_TRAINING

-- 构建最终查询语句
SET @FinalSQL = CONCAT(
    'SELECT C.ID AS ID_USER, C.USERNAME, ',
    @DynamicColumns,
    ' FROM TBL_TRAINING A
      JOIN TBL_DETAIL_TRAINING B ON B.ID_TRAINING = A.ID
      JOIN TBL_USER C ON C.ID = B.ID_USER
      GROUP BY C.ID, C.USERNAME'
)

-- 执行动态SQL
EXEC sp_executesql @FinalSQL

兼容SQL Server 2016及以下版本的代码 #

如果你的SQL Server版本不支持STRING_AGG，改用FOR XML PATH拼接列表达式：

DECLARE @DynamicColumns NVARCHAR(MAX)
DECLARE @FinalSQL NVARCHAR(MAX)

-- 用FOR XML PATH生成动态列表达式
SELECT @DynamicColumns = STUFF(
    (SELECT ', ' + CONCAT(
        'MAX(CASE WHEN A.NM_TRAINING = ''', NM_TRAINING, ''' THEN B.SCORE END) AS [', NM_TRAINING, '], ',
        'MAX(CASE WHEN A.NM_TRAINING = ''', NM_TRAINING, ''' THEN B.TIME END) AS [Time_', NM_TRAINING, ']'
    )
     FROM TBL_TRAINING
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
    1, 2, ''
)

-- 构建并执行最终查询
SET @FinalSQL = CONCAT(
    'SELECT C.ID AS ID_USER, C.USERNAME, ',
    @DynamicColumns,
    ' FROM TBL_TRAINING A
      JOIN TBL_DETAIL_TRAINING B ON B.ID_TRAINING = A.ID
      JOIN TBL_USER C ON C.ID = B.ID_USER
      GROUP BY C.ID, C.USERNAME'
)

EXEC sp_executesql @FinalSQL

原理说明 #

1. 动态列生成：遍历TBL_TRAINING表，为每个训练项生成两个连续的表达式——提取该训练的分数、提取该训练的时长，确保分数列与时长列相邻。
2. 聚合查询：按用户ID和用户名分组，用MAX聚合（因每个用户每个训练仅一条记录，MAX/MIN/SUM效果一致）提取对应数据。
3. 动态执行：将拼接好的SQL语句通过sp_executesql执行，自动适配任意数量的训练项，无需手动硬编码列名。

执行结果 #

运行后将直接生成符合预期的报表格式，每个训练的分数列与对应时长列紧密相邻。

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