Reputation: 672
In this simple Pivot Example (T-SQL), I am trying to replace the NULL with 0. I have tried all the suggestions that I found but still getting NULL. How do I replace the NULL value in a PIVOT with 0
select *
from
(
select ISNULL([vendid],0)as [vendid],isnull(origdocamt,0)as origdocamt
from APDoc
) X
pivot
(sum(origdocamt) for vendid in ([AAA],[BBB])) As P
Output
AAA BBB
45800 NULL
Upvotes: 1
Views: 617
Reputation: 7009
The NULL
's you are seeing are because of the new values:
select ISNULL([AAA],0) as [AAA], ISNULL([BBB],0) as [BBB]
from
(
select ISNULL([vendid],0)as [vendid],isnull(origdocamt,0)as origdocamt
from APDoc
) X
pivot
(sum(origdocamt) for vendid in ([AAA],[BBB])) As P
Upvotes: 0
Reputation: 247880
You will want to use IsNull
or Coalesce
to perform the replacement of null
in the final select:
select
[AAA] = IsNull([AAA], 0),
[BBB] = IsNull([BBB], 0)
from
(
select [vendid],
origdocamt
from APDoc
) X
pivot
(
sum(origdocamt)
for vendid in ([AAA],[BBB])
) As P
Upvotes: 3