如何在 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 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

我尝试使用 ISNULL 但没有成功.

I tried to use the ISNULL but did not work.

PIVOT SUM(ISNULL(DATA,0)) AS QTY

我需要使用什么语法?

推荐答案

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屋！