May 3, 2007 at 4:07 pm
I'm trying to use PIVOT with the SUM aggregation but I want to change the null values in the result set to zeros. Normally I would use the ISNULL function to do that, but it doesn't seem to work with the PIVOT operator. I'm including a simplified version of my query since the real query uses dynamic sql because I don't know all of the columns ahead of time.
This works, but gives null values:
SELECT * FROM
(SELECT fcid, species, pctcov
FROM dbo.FC_SPP_LIVE) f
PIVOT (SUM(pctcov)
FOR species IN ([abam],[psme],[arme])) e
This does not work:
SELECT * FROM
(SELECT fcid, species, pctcov
FROM dbo.FC_SPP_LIVE) f
PIVOT (SUM(ISNULL(pctcov,0))
FOR species IN ([abam],[psme],[arme])) e
I've also tried replacing ISNULL with a CASE statement, but that doesn't work either. I realize that I could take out the * and use ISNULL on each column, but as I said I don't know what all the columns will be in my real query (and there are likely to be hundreds of them).
Is there something simple that I'm overlooking, or is it impossible to replace the nulls with zeros without addressing each column individually?
May 3, 2007 at 5:17 pm
Either of these should work fine...
SELECT
fcid
,species
,ISNULL([abam], 0) AS [abam]
,ISNULL([psme], 0) AS [psme]
,ISNULL([arme], 0) AS [arme]
FROM
(SELECT
fcid
,species
,pctcov
FROM
dbo.FC_SPP_LIVE) f
PIVOT
(SUM(pctcov) FOR species IN ([abam],[psme],[arme])
) e
-- OR
SELECT
*
FROM
(SELECT
fcid
,species
,ISNULL([pctcov], 0) AS pctcov
FROM
dbo.FC_SPP_LIVE) f
PIVOT
(SUM(pctcov) FOR species IN ([abam],[psme],[arme])
) e
Basically, you can only do aggregation in the PIVOT section, so you have to move it outward.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 3, 2007 at 6:08 pm
Thanks, Jason. The first solution works, but will not work when I don't know the columns ahead of time (I'm trying to avoid having to list each column explicitly). The second solution still gives me null values in the results. I assume it's because the nulls are not in the original view but they are a result of pivoting the data.
The FC_SPP_LIVE view looks like this:
fcid, species, pctcov
1, abam, 4.5
1, psme, 3.4
2, arme, 2.3
I think the second solution would work if I change the view to include all species for each fcid like this:
fcid, species, pctcov
1, abam, 4.5
1, arme, 0.0
1, psme, 3.4
2, abam, 0.0
2, arme, 2.3
2, psme, 0.0
Well actually if I had the data in that format I wouldn't even need the second solution. However, I'm working with 65,000 plots (unique fcid's) and there are 3000 different species that may occur on a plot, so my input view would have 195,000,000 rows! Also I'm not even sure how I would create that view.
Does anyone have any other ideas how to accomplish this seemingly simple task?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply