CODEWITHSUNDEEP
sqlsql-server-2008
Milacay
Milacay

Reputation: 1497

SQL Server 2008 - Pivot Table ISNULL = 0 Not working

I am working on joining two tables and display the result in pivot table. This is my first time doing the pivot, so I am still very new to this.

I have this query below is display the result almost I what I want, but one small thing I couldn't figure out. if the value isNull, then convert it to 0. like I normally do this isNull(value, 0).

SELECT * FROM 
( 
SELECT oa.OpptID, o.OpptName, ISNULL(Amount, 0) AS Amount, BucketNameID
FROM dbo.Opportunity o 
LEFT JOIN dbo.ProductBucketAmount oa ON oa.OpptID = o.OpptID
WHERE oa.OpptID IN (123, 456) 
) p
PIVOT
(
    Sum(p.Amount)
    FOR p.BucketNameID
    IN (
        [1], [2], [3], [4], [5]
    )   
) AS pvt

If I do like this for the PIVOT columns, it will works, but looks so messy codes. Plus, I want to SELECT all columns everytime (SELECT * FROM...)

SELECT OpptName, OpptName, ISNULL([1], 0) AS '1', ISNULL([2], 0) AS '2'  ... etc ...

Please help or suggestion

Thanks,

Upvotes: 1

Views: 5004

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

Yes, you have to do it outside of the pivot, so yes, it's somewhat messy. The reason is that some of those NULLs are being constructed by the pivot from no input rows - so there's no earlier stage at which you can convert the NULLs to 0s.

And, as you've also discovered, the PIVOT clause itself isn't flexible. It has to have exactly an aggregate function specified, not an arbitrary expression containing the aggregate.

So what you have:

SELECT OpptName, OpptName, ISNULL([1], 0) AS '1', ISNULL([2], 0) AS '2'  ... etc

is about the best that you can do. (And there's no way to do this as some form of SELECT * ... - you have to convert each column separately)

Upvotes: 3

Related Questions