替换sql pivot中的空值

时间:2022-12-18 20:12:11

I have the following query :

我有以下查询:

SELECT *
FROM Table1
PIVOT
(
  SUM(Value)
  FOR [Period] IN ([06/1/2007],[07/1/2007])
)
AS p

Some of the rows returned are null but i want to replace them with 0.

返回的一些行是null但我想用0替换它们。

I've tried SUM(ISNULL(Value,0)) as Val but it's not working. ( it's saying incorrect syntax)

我已经尝试过SUM(ISNULL(Value,0))作为Val,但它不起作用。 (它说的语法不正确)

1 个解决方案

#1


22  

Ohh, I was using ISNULL in the wrong place.

哦,我在错误的地方使用ISNULL。

the query should look like this:

查询应如下所示:

SELECT ID,ISNULL([06/1/2007],0), ISNULL([07/1/2007],0)
FROM Table1
PIVOT
(
  SUM(Value)
  FOR [Period] IN ([06/1/2007],[07/1/2007])
)
AS p

#1


22  

Ohh, I was using ISNULL in the wrong place.

哦,我在错误的地方使用ISNULL。

the query should look like this:

查询应如下所示:

SELECT ID,ISNULL([06/1/2007],0), ISNULL([07/1/2007],0)
FROM Table1
PIVOT
(
  SUM(Value)
  FOR [Period] IN ([06/1/2007],[07/1/2007])
)
AS p