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 sucess.

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

Could someone please look into its syntax error? Thanks a lot!

Answers


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

Need Your Help

hibernate update hql over large set of rows

hibernate session hql

I have an hibernate HQL (oracle 10g) thats like this

angularjs http works only once

javascript jquery ajax json angularjs

I saw some similiar posts at forum but didn't find solition to my problem. Maybe it's because I'm new to js/angular and that stuff. Here's the thing:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.