'Invalid Column name for Dynamic SQL with PIVOT

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?

enter image description here

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

enter image description here



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source