Dynamic query in SSIS expression builder throwing an error expression cannot be evaluated

Question

I am working on an example to write dynamic sql to pivot the data in ssis expression builder.It fails to evaluate the expression. Below is the dynamic query:

 DECLARE @Cols as NVARCHAR(MAX)
 DECLARE @SQL as NVARCHAR(MAX)
 
SELECT @Cols = COALESCE(@Cols + ', ','') + QUOTENAME(Name)
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'

Answer 1

I think your double quotes around + @Cols needs to be single quotes, see below.

DECLARE @Cols as NVARCHAR(MAX)
 DECLARE @SQL as NVARCHAR(MAX)
 
SELECT @Cols = COALESCE(@Cols + ', ','') + QUOTENAME(Name)
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'