如何拆解动态拼接的SQL PIVOT语句 解决语法识别报错问题

错误原因 #

你直接拆解拼接片段放到静态SQL中运行会报错，是因为STUFF、REPLACE这些函数是在动态SQL的外层执行的，生成的是拼接后的列名字符串，你直接把函数逻辑写到PIVOT的IN子句中，SQL会将函数调用识别为非法的列名，自然会抛出语法错误。

正确拆解步骤 #

步骤1：导出拼接完成的完整静态SQL #

不用手动拆解拼接逻辑，直接修改原动态SQL脚本，把最后执行sp_executesql的语句替换为打印/导出@sql内容的语句即可：

-- 把原来的 EXEC sp_executesql @sql 替换为下面任意一行即可
PRINT @sql
-- 如果@sql太长SSMS打印截断，用下面的语句查询完整内容
SELECT CAST(@sql AS XML) AS FullSql

执行后就能拿到已经拼接完成、可以直接独立运行的完整SQL。

步骤2：手动拆解的操作方法 #

如果你需要手动处理那段拼接逻辑，按以下两步操作：

1. 先单独执行生成@columns的查询，拿到@columns的实际值，举个例子你拿到的结果可能是：, p.[微信支付], p.[支付宝], p.[银行卡]
2. 分别处理两处拼接位置：

• SELECT后的列部分：用STUFF(@columns, 1, 2, '')去掉前两个字符, ，得到p.[微信支付], p.[支付宝], p.[银行卡]
• PIVOT的IN子句部分：先把所有, p.[替换为,[得到,[微信支付],[支付宝],[银行卡]，再去掉第一个字符, ，最终得到[微信支付],[支付宝],[银行卡]

最终独立运行的静态SQL示例 #

SELECT [TransactionId], [微信支付],[支付宝],[银行卡] 
FROM (
    SELECT [TransactionId], [PaymentAmount] AS [Value], [Description] as [Name] 
    FROM [CNF_PaymentMethod]
    JOIN [DATA_Payment]
    ON [CNF_PaymentMethod].[PaymentMethodId] = [DATA_Payment].[PaymentMethodId]
) AS j 
PIVOT (
    SUM(Value) FOR [Name] in ([微信支付],[支付宝],[银行卡])
) AS p;

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