如何修复Synapse中动态SQL查询语法错误并实现动态列输出

错误原因 #

1. 列名不匹配：临时表#temp定义的第三列是Category，但INSERT语句中错误使用了不存在的Number列，这是触发语法错误的直接原因。
2. 非法列名：PIVOT操作中直接使用纯数字（如1、2）作为列名，不符合SQL语法规范，必须使用合法标识符（如CompanyID1）并包裹方括号。
3. 无效列引用：动态SQL的SELECT语句中引用了不存在的date列，实际应选择CxID。

修正后的完整代码 #

IF OBJECT_ID('temp..#temp') IS NOT NULL
BEGIN
    DROP TABLE #temp
END
GO

CREATE TABLE #temp
(
    [CxID] int,
    [CompanyID] int,
    [Category] int -- 该列由ROW_NUMBER() OVER(PARTITION BY [CxID] ORDER BY [CxID], [CompanyID])生成
)

-- 修正INSERT语句的列名，匹配临时表定义
INSERT INTO #temp ([CxID], [CompanyID], [Category]) VALUES (1, 101, 1);
INSERT INTO #temp ([CxID], [CompanyID], [Category]) VALUES (1, 102, 2);
INSERT INTO #temp ([CxID], [CompanyID], [Category]) VALUES (1, 103, 3);
INSERT INTO #temp ([CxID], [CompanyID], [Category]) VALUES (2, 201, 1);
INSERT INTO #temp ([CxID], [CompanyID], [Category]) VALUES (3, 301, 1);
INSERT INTO #temp ([CxID], [CompanyID], [Category]) VALUES (4, 401, 1);
INSERT INTO #temp ([CxID], [CompanyID], [Category]) VALUES (5, 501, 1);
INSERT INTO #temp ([CxID], [CompanyID], [Category]) VALUES (5, 502, 2);
INSERT INTO #temp ([CxID], [CompanyID], [Category]) VALUES (5, 503, 3);
INSERT INTO #temp ([CxID], [CompanyID], [Category]) VALUES (5, 504, 4);

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

-- 生成合法的动态列名：[CompanyID1], [CompanyID2], ...
SET @cols = (
    SELECT STRING_AGG(QUOTENAME('CompanyID' + CAST(Category AS NVARCHAR(10))), ',')
    FROM (SELECT DISTINCT Category FROM #temp WHERE Category IS NOT NULL) t
);

-- 修正动态SQL的列引用和PIVOT语法
SET @query = N'
SELECT CxID, ' + @cols + N'
FROM (
    SELECT CxID, CompanyID, ''CompanyID'' + CAST(Category AS NVARCHAR(10)) AS CategoryAlias
    FROM #temp
) x
PIVOT (
    MAX(CompanyID)
    FOR CategoryAlias IN (' + @cols + N')
) p';

EXECUTE sp_executesql @query;

关键修正说明 #

• 修正INSERT语句的列名，确保与临时表定义一致，同时去掉值的单引号（因为列类型是int）。
• 使用QUOTENAME函数生成带方括号的合法列名，避免纯数字列名的语法错误。
• 在子查询中将Category拼接为CompanyIDN格式的别名，与动态列名对应。
• 将EXECUTE(@query)改为EXECUTE sp_executesql @query，这是Azure Synapse中执行动态SQL的推荐方式，更安全且支持参数化。

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