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

@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

"tshad" <tfs (AT) dslextreme (DOT) com> wrote

On 2010-10-26 21:37, tshad wrote:
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 don't 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

"Lennart Jonsson" <erik.lennart.jonsson (AT) gmail (DOT) com> wrote

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

tshad (tfs (AT) dslextreme (DOT) 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'm afraid
that I have never bothered to learn that syntax.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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

On Tue, 26 Oct 2010 12:37:02 -0700, tshad wrote:

Hi Tom,

I'm 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