如何在 PIVOT 中用 0 输出替换(空)值

2022-01-22 00:00:00 pivot sql sql-server

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

相关文章