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