T-SQL中带分组功能的动态透视表（Pivot）查询实现问题

实现思路 #

• 首先为同ID分组内的每条记录生成唯一序号，该序号对应最终结果的扩展列名
• 拼接Label和所有Tag字段为你需要的标签 (tag1,tag2,tag3,tag4)格式的内容
• 使用动态SQL生成适配任意数量扩展列的Pivot查询，无需提前硬编码列数

实现代码 #

假设你的源表名为TagData，可根据实际情况修改表名：

DECLARE 
    @columnList NVARCHAR(MAX),
    @query NVARCHAR(MAX)

-- 动态生成所有扩展列的列名
SELECT @columnList = COALESCE(@columnList + ',', '') + QUOTENAME(rowSeq)
FROM (
    SELECT DISTINCT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Label) AS rowSeq
    FROM TagData
) AS seqTable
ORDER BY rowSeq

-- 组装最终执行的Pivot查询
SET @query = N'
SELECT ID, ' + @columnList + N'
FROM (
    SELECT 
        ID,
        -- 拼接目标格式内容，SQL Server 2012及以上支持CONCAT函数
        CONCAT(Label, '' ('', Tag1, '','', Tag2, '','', Tag3, '','', Tag4, '')'') AS content,
        ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Label) AS rowSeq
    FROM TagData
) AS sourceTable
PIVOT(
    -- 每个ID+rowSeq只有一条记录，MAX聚合不会改变内容
    MAX(content)
    FOR rowSeq IN (' + @columnList + N')
) AS pivotTable
ORDER BY ID'

-- 执行查询
EXEC sp_executesql @sql

兼容说明 #

如果使用的SQL Server版本低于2012不支持CONCAT函数，将拼接部分替换为以下写法即可：

Label + ' (' 
+ CAST(Tag1 AS VARCHAR(10)) + ',' 
+ CAST(Tag2 AS VARCHAR(10)) + ',' 
+ CAST(Tag3 AS VARCHAR(10)) + ',' 
+ CAST(Tag4 AS VARCHAR(10)) 
+ ')' AS content

调整说明 #

• 若需要修改同ID下记录的排列顺序，只需修改ROW_NUMBER()中的ORDER BY子句
• 若Tag列数量有增减，调整拼接内容部分的对应字段即可

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