SSIS表达式生成器中动态查询报错：表达式无法求值

核心问题点 #

• 字段引用错误：子查询选取Category，但外层使用了QUOTENAME(Name)，字段名不匹配
• SQL拼接语法错误：PIVOT部分的列名列表拼接引号写法错误，导致动态列无法正确插入
• 缺少执行语句：原代码仅生成动态SQL，未执行该语句

修正后的SQL代码（适用于Execute SQL Task） #

DECLARE @Cols as NVARCHAR(MAX)
DECLARE @SQL as NVARCHAR(MAX)

SELECT @Cols = COALESCE(@Cols + ', ','') + QUOTENAME(Category)
FROM
   (
     SELECT DISTINCT Category
     FROM Product
   ) As t1

SET @SQL = 'SELECT *
FROM
(
    SELECT ProductID,
    Category,
    Quantity
    FROM   Product
) as PivotData
PIVOT
(
   COUNT(Quantity)
   FOR Category IN (' + @Cols + ')
) AS PivotResult'

EXEC sp_executesql @SQL

SSIS表达式生成器专用版本（转义单引号） #

如果是在SSIS表达式中构建该动态SQL作为变量值，需将内部单引号转义为两个单引号，表达式如下：

"DECLARE @Cols as NVARCHAR(MAX)
DECLARE @SQL as NVARCHAR(MAX)

SELECT @Cols = COALESCE(@Cols + '', '','') + QUOTENAME(Category)
FROM
   (
     SELECT DISTINCT Category
     FROM Product
   ) As t1

SET @SQL = ''SELECT *
FROM
(
    SELECT ProductID,
    Category,
    Quantity
    FROM   Product
) as PivotData
PIVOT
(
   COUNT(Quantity)
   FOR Category IN ('' + @Cols + '')
) AS PivotResult''

EXEC sp_executesql @SQL"

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