Invalid Column name for Dynamic SQL with PIVOT

Question

I am trying to get monthly sales for different desserts and pivot them on columns. The column names are defined in @columns and shown in the attachment. The error says invalid column names, I have extracted the column names from MenuItems table and are displayed as they are. How can I fix this?

DECLARE @columns NVARCHAR(MAX) = '';
SELECT 
    @columns += QUOTENAME([Name]) + ', '
FROM 
    dbo.MenuItems 
WHERE 
    [GroupCode] = 'Desserts';
SET @columns = LEFT(@columns, LEN(@columns) - 1);
DECLARE @sql NVARCHAR(MAX) = '';
PRINT @columns;
SET @sql = '
    WITH monthly_desserts as (
        SELECT 
            YEAR(ord.[LastUpdateDateTime]) AS [Year],
            MONTH(ord.[LastUpdateDateTime]) AS [Month],
            ord.[MenuItemName] AS [ItemName],
            ISNULL(SUM(ord.[Quantity]), 0) AS [Qty], 
            ISNULL(SUM(ord.[Quantity] * ord.[Price]), 0) AS [Revenue]
        FROM
            dbo.Orders ord
        WHERE 
            ord.[MenuItemName] IN (' + @columns + ')
        GROUP BY
            ord.[MenuItemName],
            YEAR(ord.[LastUpdateDateTime]),
            MONTH(ord.[LastUpdateDateTime])
    ) select 
        [Year], 
        [Month],
        ' + @columns + '
    FROM 
        monthly_desserts
    PIVOT (
        SUM([Revenue])
        FOR [ItemName] IN (' + @columns + ')
    ) pvt
    ORDER BY
        [Year] DESC, [Month] DESC
';

execute(@sql);

Looking for results in following manner

which RDBMS are you using? MySQL,SQL Server,postgresql,..... . tag it correctly — RF1991, Apr 13 '22 at 11:04
Providing text and code instead of images of code helps to get much faster recommendations from the community — RF1991, Apr 13 '22 at 11:04
`ord.[MenuItemName] IN (' + @columns + ')` becomes `ord.[MenuItemName] IN ([Strawberry sundae])`. Because of `[]` you are looking for columns with that name. `print @sql` Should always be involved in writing dynamic sql — HoneyBadger, Apr 13 '22 at 11:08
Side note: Using variable assignment to concatenate strings (SELECT @Column += ....) is [undocumented and unreliable](https://marc.durdin.net/2015/07/concatenating-strings-in-sql-server-or-undefined-behaviour-by-design/). It does not always work as expected. You should either use STRING_AGG (if using a version that supports it), or use [XML Extensions](https://stackoverflow.com/a/5031297/1048425) to concatenate the rows into a variable — GarethD, Apr 13 '22 at 11:17
Please provide sample data (no screenshots) to test your script, then we'll advise accordingly... — Attie Wagner, Apr 13 '22 at 11:36
@GarethD is right, avoid use of undocumented method, additionally try change the size of variable columns from NVARCHAR(MAX) to NVARCHAR(4000) or some reasonable size, because there are some strange behavior previously reported when concatenating in this way, please check the link https://stackoverflow.com/questions/4780513/varcharmax-acting-weird-when-concatenating-string — Felicio Balane, Apr 13 '22 at 12:45
..Or better use undocumented method with caution, sometimes it's easy to use them. — Felicio Balane, Apr 13 '22 at 13:44

Invalid Column name for Dynamic SQL with PIVOT

0 Answers0