Azure Synapse中Dynamic Pivot T-SQL实现问题求助

你遇到的第一个错误是因为Azure Synapse SQL池不支持SQL Server中那种通过SELECT语句累加变量赋值的语法（SELECT @cols = @cols + ... FROM ...），必须用STRING_AGG函数来拼接列名，同时要注意几个关键点：

• 必须对列名使用QUOTENAME包裹，避免特殊字符导致语法错误
• 利用columnNumber排序，解决你提到的列顺序问题
• 动态SQL的执行在Synapse中可以直接用EXEC，但要确保拼接的语句正确

下面是修正后的完整可运行代码：

CREATE TABLE #temp
(
    columnName VARCHAR(MAX),
    columnNumber INTEGER,
    cellValue VARCHAR(MAX),
    rowNumber INTEGER,
)

INSERT INTO #temp VALUES ('xxx_(col1)', 1, 'dajkhdjkas', 1)
INSERT INTO #temp VALUES ('abc_(col2)', 2, '31209839043123', 1)
INSERT INTO #temp VALUES ('yyy_(col3)', 3, 'dddddddddd', 1)
INSERT INTO #temp VALUES ('xxx_(col1)', 1, 'cxczxczxcz', 2)
INSERT INTO #temp VALUES ('abc_(col2)', 2, '31890839012433', 2)
INSERT INTO #temp VALUES ('yyy_(col3)', 3, 'aaaaaaaaaa', 2)

DECLARE @cols AS NVARCHAR(MAX) = '';
DECLARE @query AS NVARCHAR(MAX) = '';

-- 使用STRING_AGG拼接带QUOTENAME的列名，并按columnNumber排序解决列顺序问题
SELECT @cols = STRING_AGG(QUOTENAME(columnName), ',') WITHIN GROUP (ORDER BY columnNumber)
FROM (SELECT DISTINCT columnNumber, columnName FROM #temp) AS tmp

SET @query = N'
SELECT rowNumber, ' + @cols + N' 
FROM (
    SELECT columnName, cellValue, rowNumber 
    FROM #temp
) src
PIVOT (
    MAX(cellValue) FOR columnName IN (' + @cols + N')
) piv'

EXEC (@query)

DROP TABLE #temp

关键修正点说明： #

• 用STRING_AGG(QUOTENAME(columnName), ',') WITHIN GROUP (ORDER BY columnNumber)替代原有的变量累加，既完成列名拼接，又通过columnNumber保证列顺序正确，解决你提到的MSSQL中列排序的问题
• 确保动态SQL字符串用N前缀标记为Unicode字符串，避免字符编码问题
• Synapse中执行动态SQL可以直接使用EXEC (@query)，无需额外调整

内容的提问来源于stack exchange，提问作者F.R.