Azure SQL DB中多个WITH语句报错，请求排查解决

错误原因 #

你遇到的Msg 102错误核心原因是：T-SQL中CTE（公共表表达式）仅作为临时数据集定义，必须在WITH子句之后紧跟一个实际调用这些CTE的SQL语句（SELECT/INSERT/UPDATE/DELETE等）。你的代码只定义了两个CTE，但没有任何使用它们的逻辑，因此数据库引擎解析时会认为语法不完整，报错指向最后一个右括号。

修正后的代码示例 #

根据你后续要做PIVOT的需求，这里提供两种常见的修正方式：

方式1：合并两个CTE数据后做PIVOT #

如果需要将Default和FOOBAR两组数据合并，再进行透视操作，可以用UNION ALL合并两个CTE，然后执行PIVOT：

WITH EpicBenefitsData1 ("Epic Benefits Field Name", "Epic Benefits Field Value", "FK Epic ID") AS 
(
    SELECT 
        "Epic Benefits Field Name", "Epic Benefits Field Value", "FK Epic ID"
    FROM 
        [current_dw].[Epic Benefits] AS EpicBenefits1 
    WHERE 
        EpicBenefits1.[Epic Benefits Field Set Name] = 'Default'
),
EpicBenefitsData2 ("Epic Benefits Field Name", "Epic Benefits Field Value", "FK Epic ID") AS 
(
    SELECT 
        "Epic Benefits Field Name", "Epic Benefits Field Value", "FK Epic ID"
    FROM 
        [current_dw].[Epic Benefits] AS EpicBenefits2 
    WHERE 
        EpicBenefits2.[Epic Benefits Field Set Name] = 'FOOBAR'
)
-- 必须添加使用CTE的语句，这里合并后做PIVOT
SELECT *
FROM (
    SELECT * FROM EpicBenefitsData1
    UNION ALL
    SELECT * FROM EpicBenefitsData2
) CombinedData
PIVOT (
    MAX("Epic Benefits Field Value")
    FOR "Epic Benefits Field Name" IN ([Field1], [Field2], [Field3]) -- 替换为实际要透视的字段名
) PivotedData;

方式2：分别使用两个CTE做PIVOT #

如果需要对两组数据单独进行透视，可以分别调用两个CTE：

WITH EpicBenefitsData1 ("Epic Benefits Field Name", "Epic Benefits Field Value", "FK Epic ID") AS 
(
    SELECT 
        "Epic Benefits Field Name", "Epic Benefits Field Value", "FK Epic ID"
    FROM 
        [current_dw].[Epic Benefits] AS EpicBenefits1 
    WHERE 
        EpicBenefits1.[Epic Benefits Field Set Name] = 'Default'
),
EpicBenefitsData2 ("Epic Benefits Field Name", "Epic Benefits Field Value", "FK Epic ID") AS 
(
    SELECT 
        "Epic Benefits Field Name", "Epic Benefits Field Value", "FK Epic ID"
    FROM 
        [current_dw].[Epic Benefits] AS EpicBenefits2 
    WHERE 
        EpicBenefits2.[Epic Benefits Field Set Name] = 'FOOBAR'
)
-- 调用第一个CTE做PIVOT
SELECT *
FROM EpicBenefitsData1
PIVOT (
    MAX("Epic Benefits Field Value")
    FOR "Epic Benefits Field Name" IN ([DefaultField1], [DefaultField2])
) PivotedDefault;

-- 可以再单独调用第二个CTE
SELECT *
FROM EpicBenefitsData2
PIVOT (
    MAX("Epic Benefits Field Value")
    FOR "Epic Benefits Field Name" IN ([FoobarField1], [FoobarField2])
) PivotedFoobar;

额外提示 #

• 列名包含空格时，使用双引号需要确保数据库的QUOTED_IDENTIFIER选项为ON（Azure SQL默认开启），也可以改用方括号[]包裹列名，兼容性更强。
• 多CTE定义时，只需要一个WITH关键字，后续CTE用逗号分隔，你的这部分语法是正确的，问题仅在于缺少后续的调用语句。

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