How to replace (null) values with 0 output in PIVOT

Question

I tried to convert the (null) values with 0 (zeros) output in PIVOT function but have no success.

Below is the table and the syntax I've tried:

SELECT
CLASS,
[AZ],
[CA],
[TX]
FROM #TEMP
PIVOT (SUM(DATA)
FOR STATE IN ([AZ], [CA], [TX])) AS PVT
ORDER BY CLASS

CLASS   AZ  CA      TX
RICE    10  4       (null)
COIN    30  3        2
VEGIE   (null) (null) 9

I tried to use the ISNULL but did not work.

PIVOT SUM(ISNULL(DATA,0)) AS QTY

What syntax do I need to use?

Answer 1

SELECT CLASS,
isnull([AZ],0),
isnull([CA],0),
isnull([TX],0)
FROM #TEMP
PIVOT (SUM(DATA)
FOR STATE IN ([AZ], [CA], [TX])) AS PVT
ORDER BY CLASS

Answer 2

If you have a situation where you are using dynamic columns in your pivot statement you could use the following:

DECLARE @cols               NVARCHAR(MAX)
DECLARE @colsWithNoNulls    NVARCHAR(MAX)
DECLARE @query              NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(Name) 
            FROM Hospital
            WHERE Active = 1 AND StateId IS NOT NULL
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @colsWithNoNulls = STUFF(
            (
                SELECT distinct ',ISNULL(' + QUOTENAME(Name) + ', ''No'') ' + QUOTENAME(Name)
                FROM Hospital
                WHERE Active = 1 AND StateId IS NOT NULL
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

EXEC ('
        SELECT Clinician, ' + @colsWithNoNulls + '
        FROM
        (
            SELECT DISTINCT p.FullName AS Clinician, h.Name, CASE WHEN phl.personhospitalloginid IS NOT NULL THEN ''Yes'' ELSE ''No'' END AS HasLogin
            FROM Person p
            INNER JOIN personlicense pl ON pl.personid = p.personid
            INNER JOIN LicenseType lt on lt.licensetypeid = pl.licensetypeid
            INNER JOIN licensetypegroup ltg ON ltg.licensetypegroupid = lt.licensetypegroupid
            INNER JOIN Hospital h ON h.StateId = pl.StateId
            LEFT JOIN PersonHospitalLogin phl ON phl.personid = p.personid AND phl.HospitalId = h.hospitalid
            WHERE ltg.Name = ''RN'' AND
                pl.licenseactivestatusid = 2 AND
                h.Active = 1 AND
                h.StateId IS NOT NULL
        ) AS Results
        PIVOT
        (
            MAX(HasLogin)
            FOR Name IN (' + @cols + ')
        ) p
')

Answer 3

You cannot place the IsNull() until after the data is selected so you will place the IsNull() around the final value in the SELECT:

SELECT CLASS,
  IsNull([AZ], 0) as [AZ],
  IsNull([CA], 0) as [CA],
  IsNull([TX], 0) as [TX]
FROM #TEMP
PIVOT 
(
  SUM(DATA)
  FOR STATE IN ([AZ], [CA], [TX])
) AS PVT
ORDER BY CLASS

Answer 4

Sometimes it's better to think like a parser, like T-SQL parser. While executing the statement, parser does not have any value in Pivot section and you can't have any check expression in that section. By the way, you can simply use this:

SELECT  CLASS
,   IsNull([AZ], 0)
,   IsNull([CA], 0)
,   IsNull([TX], 0)
    FROM #TEMP
    PIVOT (
        SUM(DATA)
        FOR STATE IN (
            [AZ]
        ,   [CA]
        ,   [TX]
        )
    )   AS  PVT
    ORDER   BY  CLASS

Answer 5

You have to account for all values in the pivot set. you can accomplish this using a cartesian product.

select pivoted.*
from (
    select cartesian.key1, cartesian.key2, isnull(relationship.[value],'nullvalue') as [value]
    from (
      select k1.key1, k2.key2
      from ( select distinct key1 from relationship) k1
          ,( select distinct key2 from relationship) k2
    ) cartesian
      left outer join relationship on relationship.key1 = cartesian.key1 and  relationship.key2 = carterisan.key2
) data
  pivot (
    max(data.value) for ([key2_v1], [key2_v2], [key2_v3], ...)
  ) pivoted

Answer 6

I have encountered a similar problem. The root cause is that (use your scenario for my case), in the #temp table, there is no record for:

a. CLASS=RICE and STATE=TX
b. CLASS=VEGIE and (STATE=AZ or STATE=CA)

So, when MSSQL does pivot for no record, MSSQL always shows NULL for MAX, SUM, ... (aggregate functions).

None of above solutions (IsNull([AZ], 0)) works for me, but I do get ideas from these solutions.

Sorry, it really depends on the #TEMP table. I can only provide some suggestions.

1. Make sure #TEMP table have records for below condition, even Data is null.

   a. CLASS=RICE and STATE=TX
   b. CLASS=VEGIE and (STATE=AZ or STATE=CA)

   You may need to use cartesian product: select A.*, B.* from A, B

2. In the select query for #temp, if you need to join any table with WHERE, then would better put where inside another sub select query. (Goal is 1.)

3. Use isnull(DATA, 0) in #TEMP table.

4. Before pivot, make sure you have achieved Goal 1.

I can't give an answer to the original question, since there is no enough info for #temp table. I have pasted my code as example here.

SELECT * FROM (
            SELECT eeee.id as enterprise_id
            , eeee.name AS enterprise_name
            , eeee.indicator_name
            , CONVERT(varchar(12) , isnull(eid.[date],'2019-12-01') , 23) AS data_date
            , isnull(eid.value,0) AS indicator_value
            FROM (select ei.id as indicator_id, ei.name as indicator_name, e.* FROM　tbl_enterprise_indicator ei, tbl_enterprise e) eeee                                             
            LEFT JOIN  (select * from　tbl_enterprise_indicator_data WHERE [date]='2020-01-01') eid
                    ON  eeee.id = eid.enterprise_id and eeee.indicator_id = enterprise_indicator_id
        ) AS P 
        PIVOT 
        (
            SUM(P.indicator_value) FOR P.indicator_name IN(TX,CA)
        ) AS T 

Answer 7

To modify the results under pivot, you can put the columns in the selected fields and then modify them accordingly. May be you can use DECODE for the columns you have built using pivot function.

• Kranti A