Replace NULL with 0 in Pivot Table combined with avg CAST

Question

I'm trying to replace NULL to 0 in the output of my TagValue column combined with avg Cast. I tried the following query:

ISNULL(TRY_CAST(TagValue AS DECIMAL(18,2)),0) AS TagValue 

in the code

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT  ',' + QUOTENAME(TagID)
                      FROM [table]
                      GROUP BY TagID
                      ORDER BY TagID
                FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')

SET @query = 'SELECT   TimeStamp,  ' + @cols + ' from 
             (
                select  DATEADD(minute,DATEDIFF(minute,0,TimeStamp)/5*5,0) AS  TimeStamp, TagID ,   ISNULL(TRY_CAST(TagValue AS DECIMAL(18,2)),0) AS TagValue 
                from [table]
                 -- Where TagValue isnull(TagValue,1)=0
                Group By datediff(minute, 0,Timestamp)/5,TagID, TagValue
            ) x
            pivot 
            (
                AVG(TagValue)
                for TagID in ( ' + @cols + ' )
            ) p '

EXEC(@query)

although there are no errors the query output keeps showing NULL.

I also tried the following queries:

SELECT @cols = STUFF((SELECT  ','', IsNull(' + QUOTENAME(TagID)+', 0) as '+QUOTENAME(TagID)
                      FROM [table]
                      GROUP BY TagID
                      ORDER BY TagID
                FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')
...

SET @query = 'SELECT TimeStamp,  ' +ISNULL(@cols,0) + ' from ...

Also the Case function:

CASE When (TagValue= NULL) THEN 0 ELSE TRY_CAST(TagValue AS DECIMAL(18,2)) END AS TagValue

Would very appreciate your help! Thanks a lot

Answer 1

The PIVOT operator works on a set of constant values. These values must be explicitly written and can't be reference to another columns, contain expressions or functions like ISNULL.

DECLARE @Mascot TABLE (
    Amount INT,
    Mascot VARCHAR(100))

INSERT INTO @Mascot (
    Amount,
    Mascot)
VALUES
    (10, 'Dog'), (5, 'Dog'),
    (6, 'Cat'),
    (12, 'Weird Spider'), (8, 'Weird Spider'), (5, 'Weird Spider')

SELECT
    P.*
FROM
    @Mascot AS M
    PIVOT (
        SUM(M.Amount)
        FOR Mascot IN ([Dog], [Cat], [Weird Spider], [Cameleon])
    ) AS P

Result:

Dog     Cat     Weird Spider    Cameleon
15      6       25              NULL

After the table is pivoted, these constant values are now columns, with their name as the constant we used to pivot them:

SELECT
    P.Dog, -- We can reference the pivoted columns by name
    P.Cat,
    P.[Weird Spider],
    P.Cameleon
FROM
    @Mascot AS M
    PIVOT (
        SUM(M.Amount)
        FOR Mascot IN ([Dog], [Cat], [Weird Spider], [Cameleon])
    ) AS P

We can do any modification we want as an expression on the SELECT column list:

SELECT
    P.Dog,
    P.Cat,
    P.[Weird Spider],
    ISNULL(P.Cameleon, 0) AS Cameleon,
    Total = 
        ISNULL(P.Dog, 0) +
        ISNULL(P.Cat, 0) +
        ISNULL(P.[Weird Spider], 0) +
        ISNULL(P.Cameleon, 0)
FROM
    @Mascot AS M
    PIVOT (
        SUM(M.Amount)
        FOR Mascot IN ([Dog], [Cat], [Weird Spider], [Cameleon])
    ) AS P

Result:

Dog     Cat     Weird Spider    Cameleon    Total
15      6       25              0           46

So when you build your dynamic pivot, the pivot values must be the exact content of the value stored in the set that you are gonna pivot ('Dog', 'Cat', etc.), while on the SELECT list you can build any expression you want (ISNULL([Dog], 0) AS [Dog]).

---

So the solution for your case is to use 2 different @cols, one with the null checks on the SELECT list and another to pivot the values.

DECLARE @cols_select AS NVARCHAR(MAX),
        @cols_pivot AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX)

SELECT @cols_pivot = STUFF((SELECT  ',' + QUOTENAME(TagID)
                      FROM [table]
                      GROUP BY TagID
                      ORDER BY TagID
                FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')

SELECT @cols_select = STUFF((SELECT  ','', IsNull(' + QUOTENAME(TagID)+', 0) as '+QUOTENAME(TagID)
                      FROM [table]
                      GROUP BY TagID
                      ORDER BY TagID
                FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')

SET @query = 'SELECT   TimeStamp,  ' + @cols_select + ' from 
             (
                select  DATEADD(minute,DATEDIFF(minute,0,TimeStamp)/5*5,0) AS  TimeStamp, TagID ,   ISNULL(TRY_CAST(TagValue AS DECIMAL(18,2)),0) AS TagValue 
                from [table]
                 -- Where TagValue isnull(TagValue,1)=0
                Group By datediff(minute, 0,Timestamp)/5,TagID, TagValue
            ) x
            pivot 
            (
                AVG(TagValue)
                for TagID in ( ' + @cols_pivot + ' )
            ) p '

EXEC(@query)