CODEWITHSUNDEEP
t-sql
stackuser
stackuser

Reputation: 672

Pivot and Null Value

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

Answers (2)

Yosi Dahari
Yosi Dahari

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

Taryn
Taryn

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

Related Questions