SQL Server如何实现含3000个TargetID的表动态行转列操作

你遇到的是典型的行转列场景，由于TargetID数量多无法硬编码，使用动态SQL生成PIVOT查询即可实现，具体代码如下：

兼容所有SQL Server版本的写法 #

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX)

-- 自动拼接所有目标列名，无需手动枚举TargetID
SELECT @cols = STUFF((SELECT ',' + QUOTENAME('Target' + TargetID) 
                    FROM TT
                    GROUP BY TargetID
                    ORDER BY TargetID
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

-- 生成PIVOT查询语句
SET @query = N'SELECT RowID, ' + @cols + N' 
            FROM 
            (
                SELECT RowID, Actual, ''Target'' + TargetID AS TargetCol
                FROM TT
            ) x
            PIVOT 
            (
                MAX(Actual)
                FOR TargetCol IN (' + @cols + N')
            ) p 
            ORDER BY RowID'

-- 执行动态生成的查询
EXEC sp_executesql @query

SQL Server 2017及以上版本简化写法 #

如果你使用的是2017及以上版本，可以用STRING_AGG替代繁琐的XML拼接逻辑：

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX)

SELECT @cols = STRING_AGG(QUOTENAME('Target' + TargetID), ',') WITHIN GROUP (ORDER BY TargetID)
FROM (SELECT DISTINCT TargetID FROM TT) t

SET @query = N'SELECT RowID, ' + @cols + N' 
            FROM 
            (
                SELECT RowID, Actual, ''Target'' + TargetID AS TargetCol
                FROM TT
            ) x
            PIVOT 
            (
                MAX(Actual)
                FOR TargetCol IN (' + @cols + N')
            ) p 
            ORDER BY RowID'

EXEC sp_executesql @query

注意事项 #

如果业务中存在同一个RowID+同一个TargetID对应多条Actual记录的情况，需要先根据业务规则对重复数据做去重或聚合处理，再执行行转列逻辑，避免最终结果不符合预期。

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