CODEWITHSUNDEEP
sql-servernullpivot
Iulian
Iulian

Reputation: 1200

replace null values in sql pivot

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.

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

Upvotes: 11

Views: 31958

Answers (1)

Iulian
Iulian

Reputation: 1200

Ohh, I was using ISNULL in the wrong place.

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

Upvotes: 22

Related Questions