如何在PIVOT中用0输出替换(空)值 [英] How to replace (null) values with 0 output in PIVOT

问题描述

我尝试在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

这篇关于如何在PIVOT中用0输出替换(空)值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！