Nulls to 0 in Pivot

Giganews Newsgroups
Subject: Nulls to 0 in Pivot
Posted by:  tshad (t…@dslextreme.com)
Date: Tue, 26 Oct 2010

I am using a pivot (dynamically) and am getting NULL results for some
values.

I want to change the NULLs to 0, but keep getting errors.

In the following: @strList contains the pivot columns.

This one works fine except for the nulls:

SET @sql = '
select * from #tfs
PIVOT
(
    SUM(Number)
    for retailer in('+@strList+')
) as pvt
'

I tried:

SET @sql = '
select * from #tfs
PIVOT
(
    SUM(Number)
    for ISNULL(retailer,0) in('+@strList+')
) as pvt
'
This gets me "incorrect syntax errors"

I also tried

SET @sql = '
select * from #tfs
PIVOT
(
    ISNULL(SUM(Number),0)
    retailer in('+@strList+')
) as pvt
'
This one gets 'isnull' is not a recognized aggregate function.

Thanks,

Tom

Replies