SQL Server - Nulls to 0 in Pivot

Asked By tshad on 26-Oct-10 03:06 PM
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




tshad replied to tshad on 26-Oct-10 03:37 PM
@sql comes up with the following:

select * from #tfs
PIVOT
(
SUM(Number)
for retailer in([Sears], [CompUSA], [Lowes], [None], [K-Mart])
) as pvt

Thanks,

Tom
Lennart Jonsson replied to tshad on 26-Oct-10 04:07 PM
I would try something like:

select * from #tfs
PIVOT
(
SUM(coalesce(Number, 0))
for retailer in([Sears], [CompUSA], [Lowes], [None], [K-Mart])
) as pvt

Not sure whether coalesce is coalesce or something else in sql-server
though. The function should return the leftmost argument that is not
null, in this case if Number is null -> 0.

Since I do not know the pivot function, there is a possibility that the
null you see origins from it, and if that is the case the solution above
wont work.

/Lennart
tshad replied to Lennart Jonsson on 26-Oct-10 04:21 PM
I tried that.  I get the same error I got for ISNULL:

Incorrect syntax near the keyword 'coalesce'.


That is what I see

I would think that would be a real problem if you want 0 to show up, such as
for copying to a spreadsheet.

Tom
Erland Sommarskog replied to tshad on 26-Oct-10 06:00 PM
tshad (tfs@dslextreme.com) writes:

Have a look at http://www.sommarskog.se/dynamic_sql.html#Crosstab to
get the pattern for crosstab queries. The section also includs a link
to Itzik Ben-Gan's sp_pivot.

The PIVOT keyword does not appear in any of the places. I am afraid
that I have never bothered to learn that syntax.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
Hugo Kornelis replied to tshad on 27-Oct-10 05:59 AM
Hi Tom,

I am not very familiar with the PIVOT syntax either (like Erland, I
prefer to roll my own). But I *think* you need to use COALESCE on the
outer query:

SELECT COALESCE([Sears], 0) AS [Sears],
COALESCE([CompUSA], 0) AS [CompUSA],
...
FROM #tfs PIVOT (SUM (Number)
FOR retailer IN ([Sears], [CompUSA], ...)
) AS pvt;

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis