SQL Server动态透视查询报错：Invalid column name 'Amount2'

问题背景 #

运行动态PIVOT查询时收到错误提示：

Invalid column name 'Amount2'.

样本数据 #

CREATE TABLE Sales (
    ProductName VARCHAR(50),
    Region VARCHAR(50),
    Month2 VARCHAR(50),
    Amount2 DECIMAL(10,2)
);

INSERT INTO Sales VALUES 
       ('Product A', 'North', 'Jan', 1000),
       ('Product A', 'North', 'Feb', 2000),
       ('Product A', 'South', 'Jan', 3000),
       ('Product A', 'South', 'Feb', 4000),
       ('Product B', 'North', 'Jan', 5000),
       ('Product B', 'North', 'Feb', 6000),
       ('Product B', 'South', 'Jan', 7000),
       ('Product B', 'South', 'Feb', 8000);

原执行代码 #

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

SET @cols = STUFF((SELECT ',' + QUOTENAME(Month2) 
                   FROM Sales
                   group by Month2
                   FOR XML PATH(''), TYPE
                   ).value('.', 'NVARCHAR(MAX)') 
                  ,1,1,'')

SET @query = 'SELECT ProductName, Region, ' + @cols + ', 
                    SUM(Amount2) AS Total, 
                    SUM(SUM(Amount2)) OVER (PARTITION BY Region) AS RegionTotal,
                    SUM(SUM(Amount2)) OVER () AS GrandTotal
             FROM (
                SELECT ProductName, Region, Month2, Amount2
                FROM Sales
             ) AS s
             PIVOT (
                SUM(Amount2)
                FOR Month2 IN (' + @cols + ')
             ) AS p
             GROUP BY ProductName, Region, ' + @cols + ' WITH ROLLUP';


execute sp_executesql @query;

错误原因 #

执行PIVOT操作后，原表中的Amount2列已被聚合转换为对应月份的列（如Jan、Feb），PIVOT结果集p中不再存在Amount2列，后续的SUM(Amount2)自然找不到该列。

修正方案 #

将SUM(Amount2)替换为对PIVOT生成的月份列求和，同时调整ROLLUP的分组逻辑以匹配预期结果：

修正后的代码 #

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

SET @cols = STUFF((SELECT ',' + QUOTENAME(Month2) 
                   FROM Sales
                   GROUP BY Month2
                   FOR XML PATH(''), TYPE
                   ).value('.', 'NVARCHAR(MAX)') 
                  ,1,1,'')

-- 生成月份列的求和表达式，用于计算Total
SET @sum_cols = STUFF((SELECT ' + ISNULL(' + QUOTENAME(Month2) + ', 0)' 
                       FROM Sales
                       GROUP BY Month2
                       FOR XML PATH(''), TYPE
                       ).value('.', 'NVARCHAR(MAX)') 
                      ,1,1,'')

SET @query = 'SELECT 
                 CASE WHEN GROUPING(ProductName) = 1 THEN ''Total'' ELSE ProductName END AS ProductName,
                 Region,
                 ' + @cols + ',
                 ' + @sum_cols + ' AS Total
              FROM (
                 SELECT ProductName, Region, Month2, Amount2
                 FROM Sales
              ) AS s
              PIVOT (
                 SUM(Amount2)
                 FOR Month2 IN (' + @cols + ')
              ) AS p
              GROUP BY ProductName, Region, ' + @cols + ' WITH ROLLUP
              HAVING GROUPING(Region) = 0 OR GROUPING(ProductName) = 1;';

EXECUTE sp_executesql @query;

预期结果 #

ProductName  Region Jan    Feb   Total
Product A    North  1000   2000  3000
Product A    South  3000   4000  7000
Product B    North  5000   6000  11000
Product B    South  7000   8000  15000
Total               16000  20000  36000

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