如何在PIVOT中用0输出替换(空)值
问题描述：
我尝试在PIVOT函数中将输出的(空)值转换为0(零)，但是没有成功.
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
我尝试使用ISNULL，但是没有用.
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!
答
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