如何在SQL Server中创建列名未知的动态数据透视（Pivot）查询？

在Access中，用TRANSFORM语句可以快速实现各客户每年销售额的透视统计：

TRANSFORM Sum(dbo_HISTORY.NET_SALES) AS SumOfNET_SALES
SELECT dbo_HISTORY.CUSTOMER_NAME
FROM dbo_HISTORY
GROUP BY dbo_HISTORY.CUSTOMER_NAME
PIVOT dbo_HISTORY.YEAR;

在SQL Server中，我们可以先写出基础的聚合查询，得到按客户和年份分组的销售额：

SELECT 
    [SPEC_MIS].DBO.HISTORY.CUSTOMER_NAME,
    [SPEC_MIS].DBO.HISTORY.YEAR,
    SUM([SPEC_MIS].DBO.HISTORY.NET_SALES) AS SALES
FROM [SPEC_MIS].DBO.HISTORY
GROUP BY CUSTOMER_NAME, YEAR
ORDER BY CUSTOMER_NAME, YEAR

要实现适配任意年份、无需每年修改语句的动态透视，需要使用动态SQL自动获取所有存在的年份并生成透视列，具体实现如下：

DECLARE @YearColumns NVARCHAR(MAX), @DynamicSQL NVARCHAR(MAX)

-- 获取所有不重复的年份，拼接成透视列格式（[2020], [2021], ...）
SELECT @YearColumns = STRING_AGG(QUOTENAME(YEAR), ', ')
FROM (SELECT DISTINCT YEAR FROM [SPEC_MIS].DBO.HISTORY) AS Years

-- 拼接动态透视SQL语句
SET @DynamicSQL = N'
SELECT CUSTOMER_NAME, ' + @YearColumns + '
FROM (
    SELECT 
        CUSTOMER_NAME,
        YEAR,
        NET_SALES
    FROM [SPEC_MIS].DBO.HISTORY
) AS SourceData
PIVOT (
    SUM(NET_SALES)
    FOR YEAR IN (' + @YearColumns + ')
) AS PivotTable
ORDER BY CUSTOMER_NAME'

-- 执行动态SQL
EXEC sp_executesql @DynamicSQL

补充说明： #

• STRING_AGG适用于SQL Server 2017及以上版本，能快速拼接年份列；如果使用2016及更早版本，可替换为FOR XML PATH的拼接方式：

SELECT @YearColumns = STUFF(
    (SELECT ', ' + QUOTENAME(YEAR) 
     FROM (SELECT DISTINCT YEAR FROM [SPEC_MIS].DBO.HISTORY) AS Years
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
    1, 2, ''
)

• 动态SQL会自动读取表中所有年份生成对应列，新增年份后无需修改语句，重新执行即可得到最新的透视结果。

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