SQL Server中CTE内使用动态PIVOT的正确语法咨询

你的代码报错核心原因是CTE的定义规则限制：每个CTE必须是一个纯表值表达式，只能包含SELECT类语句，不能在CTE内部写DECLARE变量声明、EXEC执行动态SQL这类操作。另外动态Pivot的列是运行时生成的，无法在编译阶段确定结构，也没法直接作为CTE的输入。

方案一：用临时表存储动态Pivot结果，再构建后续CTE #

先执行动态Pivot把结果存入临时表，后续CTE基于临时表查询，适合需要多次复用Pivot结果的场景：

-- 1. 生成动态列并执行Pivot，结果存入临时表
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)

-- 提取需要转置的列名（这里直接查原表，等价于你的cte1逻辑）
SELECT @cols = STUFF((
    SELECT DISTINCT ',' + QUOTENAME([type])
    FROM table1
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

-- 构建动态Pivot语句，将结果写入临时表
SET @query = 'SELECT account_id, country,' + @cols + '
INTO #PivotedData
FROM (
    SELECT TOP 100 account_id, country, type, amount
    FROM table1  
) x
PIVOT (
    SUM(amount)
    FOR [type] IN (' + @cols + ')
) p'

EXEC sp_executesql @query

-- 2. 基于临时表构建后续CTE逻辑
WITH cte3 AS (
    SELECT *
    FROM #PivotedData
    WHERE Country = 'USA'
)
SELECT * FROM cte3;  -- 这里可以添加后续的查询/处理逻辑

-- 清理临时表
DROP TABLE IF EXISTS #PivotedData;

方案二：将后续CTE逻辑整合到动态SQL中 #

如果只需要一次使用Pivot结果，可把筛选等逻辑直接写到动态SQL里，无需临时表：

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

SELECT @cols = STUFF((
    SELECT DISTINCT ',' + QUOTENAME([type])
    FROM table1
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

-- 把cte3的筛选逻辑直接整合到动态查询内（注意单引号转义用两个单引号）
SET @query = '
WITH cte3 AS (
    SELECT *
    FROM (
        SELECT TOP 100 account_id, country, type, amount
        FROM table1  
    ) x
    PIVOT (
        SUM(amount)
        FOR [type] IN (' + @cols + ')
    ) p
    WHERE Country = ''USA''
)
SELECT * FROM cte3;'

EXEC sp_executesql @query

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