在SQL Server中创建动态类透视表报表的优化方案咨询

你的多表自连接方案在ID数量较少时能用，但当同一Name下的ID变多，会需要不断增加JOIN层级，既麻烦又低效。推荐用动态PIVOT来实现，完美适配动态列的需求：

步骤1：给分组内的ID分配序号 #

先通过窗口函数给每个Name下的ID按顺序标记序号，这是转列的核心基础：

SELECT 
    Name,
    ID,
    'ID' + CAST(ROW_NUMBER() OVER(PARTITION BY Name ORDER BY ID) AS VARCHAR(10)) AS ColName
FROM TestTable

输出结果示例：

步骤2：动态构建PIVOT查询 #

因为ID数量不固定，需要先获取最大序号数，再自动拼接列名和完整SQL：

DECLARE @ColList NVARCHAR(MAX), @SQL NVARCHAR(MAX)

-- 生成需要转成列的名称列表（如[ID1],[ID2],[ID3]）
SELECT @ColList = STRING_AGG(QUOTENAME('ID' + CAST(RN AS VARCHAR(10))), ',')
FROM (
    SELECT DISTINCT ROW_NUMBER() OVER(PARTITION BY Name ORDER BY ID) AS RN
    FROM TestTable
) t
ORDER BY RN

-- 构建动态PIVOT语句，空值替换为'-'
SET @SQL = N'
SELECT 
    Name,
    ' + STRING_AGG('ISNULL(CAST(' + QUOTENAME('ID' + CAST(RN AS VARCHAR(10))) + ' AS VARCHAR(10)), ''-'') AS ' + QUOTENAME('ID' + CAST(RN AS VARCHAR(10))), ',') + '
FROM (
    SELECT 
        Name,
        ID,
        ''ID'' + CAST(ROW_NUMBER() OVER(PARTITION BY Name ORDER BY ID) AS VARCHAR(10)) AS ColName
    FROM TestTable
) src
PIVOT (
    MAX(ID) FOR ColName IN (' + @ColList + ')
) piv
ORDER BY Name'

EXEC sp_executesql @SQL

注：如果你的SQL Server版本低于2017（不支持STRING_AGG），可以用FOR XML PATH来拼接列名，替换STRING_AGG部分即可。

方案优势 #

• 性能更优：避免了多层自连接的性能损耗，数据量越大优势越明显
• 动态适配：自动根据Name对应的ID数量生成列，无需手动修改查询结构
• 代码简洁：逻辑清晰，后续维护成本低

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