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

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?

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

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
')

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

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

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

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

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