SQL Server - Using COALESCE across a Pivot with variable columns

Question

I am amending a SQL query that populates a UI table. I want to eliminate any nulls that result from the query and replace them with 0.00. Typically a COALESCE function on the final SELECT would give me what I need, but the columns in the pivot (@ListOfYears) might change (SELECT PV.* may give me 1 column or 10 columns depending on @ListofYears).

    DECLARE @ListofYears varchar(6000)
    DECLARE @SQL varchar(2000)

    SET @ListofYears = '[2021],[2022]'

    SET @SQL = '
        SELECT PV.*
        FROM
            (SELECT
                p.Pkey,
                p.Code,
                P.Name as Name,
                Price,
                GroupName
            FROM
                Catalogue C
                LEFT JOIN CatalogueDetail CD on CD.CataloguePkey=C.Pkey
                LEFT JOIN CatalogueYear CY on CY.Pkey=CD.CatalogueYearPkey where C.IsActive=1
            ) as D
        PIVOT
            (                      
                COALESCE(Sum(D.Price), 0.00) for GroupName in ( ' +  @ListofYears + ' )
            ) AS PV
        ORDER BY Pkey'

    PRINT @sql
    EXEC (@sql)

Is there any way to use COALESCE or ISNULL so that my top SELECT statement does not result in any NULLS? Putting COALESCE in the pivot results in:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ','.
Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'COALESCE'.

Answer 1

As I mentioned in the question, you need to wrap the COALESCE in the outer SELECT, not in the PIVOT. This means, in short, you need something like this:

SELECT {Other Columns},
       COALESCE([2020],0) AS [2020],
       COALESCE([2021],0) AS [2021]
       ...
FROM(SELECT p.Pkey,
            p.Code,
            P.Name as Name,
            Price,
            GroupName
    FROM dbo.Catalogue C
         LEFT JOIN dbo.CatalogueDetail CD on CD.CataloguePkey=C.Pkey
         LEFT JOIN dbo.CatalogueYear CY on CY.Pkey=CD.CatalogueYearPkey
    WHERE C.IsActive=1
    ) as D
PIVOT(SUM(D.Price)
      FOR GroupName in ([2021],[2022])) AS PV
ORDER BY Pkey;

As you are using dynamic SQL, and injecting a list into you are, unsurprisingly, finding this difficult.

In truth, I would suggest switching to conditional aggregation, as this is much easier. Non-dynamic it would look like this:

SELECT {Other Columns}
       SUM(CASE GroupName WHEN 2020 THEN D.Price ELSE 0 END) AS [2020],
       SUM(CASE GroupName WHEN 2021 THEN D.Price ELSE 0 END) AS [2021]
FROM dbo.Catalogue C
     LEFT JOIN dbo.CatalogueDetail CD on CD.CataloguePkey=C.Pkey
     LEFT JOIN dbo.CatalogueYear CY on CY.Pkey=CD.CatalogueYearPkey
WHERE C.IsActive=1
GROUP BY {Other Columns};

As you want to do this dynamically, it would look a little like this (assuming you are using SQL Server 2017+):

DECLARE @Years table (Year int);
INSERT INTO @Years (Year)
VALUES (2020),(2021);

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

DECLARE @Delim nvarchar(20) = N',' + @CRLF;

SET @SQL = (SELECT N'SELECT {Other Columns}' + @CRLF +
                   STRING_AGG(N'       SUM(CASE GroupName WHEN ' + QUOTENAME(Y.Year,'''') + N'THEN D.Price ELSE 0 END) AS ' + QUOTENAME(Y.Year),@Delim) WITHIN GROUP (ORDER BY Y.Year) + @CRLF +
                   N'FROM dbo.Catalogue C' + @CRLF +
                   N'     LEFT JOIN dbo.CatalogueDetail CD on CD.CataloguePkey=C.Pkey' + @CRLF +
                   N'     LEFT JOIN dbo.CatalogueYear CY on CY.Pkey=CD.CatalogueYearPkey' + @CRLF +
                   N'WHERE C.IsActive=1' + @CRLF +
                   N'GROUP BY {Other Columns};'
            FROM @Years Y);

EXEC sys.sp_executesql @SQL;

If you are using a version of SQL Server that isn't fully supported, you'll need to use the "old" FOR XML PATH (and STUFF) method.

^{Obviously, you'll also need to replace the parts in braces with the appropriate SQL.}