CODEWITHSUNDEEP
sql-server-2008coalesce
Big Pimpin
Big Pimpin

Reputation: 437

Show 0 Not Null With Pivot

Why is my Coalesce() statement in my initial Select statement not replacing the Null with a 0?

Select * From
(
    Select a.orderstatus As [Stat], Coalesce(Count(b.id), '0') As [Count], b.region
    From orderstatus a
    Left Join saleinfo b
    on b.orderstatus = a.orderstatus
    Group By a.orderstatus, b.region
) one
    pivot ( Max([Count]) For region In ([East], [West], [North], [South]) ) pv

Upvotes: 0

Views: 85

Answers (1)

GarethD
GarethD

Reputation: 69769

Because you are using it in the inner query, whereas the problem is that the record doesn't it exist so the PIVOT is creating the NULL after the inner query has been processed. If your query (one) returned:

Stat    Count   Region
-------------------------
Stat1   0       East
Stat2   2       East
Stat1   5       West

You will end up with a Pivot Table like

Stat    East    West    North   South
---------------------------------------
Stat1   0       5       NULL    NULL
Stat2   2       NULL    NULL    NULL

For example you get NULL for (Stat2, West) because there is no result in your subquery, so the COALESCE does not help. Your work around would be to just use COUNT in the PIVOT itself:

SELECT  pvt.OrderStatus, pvt.East, pvt.West, pvt.North, pvt.South
FROM    (   SELECT  os.OrderStatus, si.Region, si.ID
            FROM    OrderStatus AS os
                    LEFT JOIN SaleInfo AS si
                        ON si.OrderStatus = b.OrderStatus
        ) AS t
        PIVOT
        (   COUNT(ID)
            FOR Region IN ([East], [West], [North], [South])
        ) AS pvt;

Or to put the COALESCE in the outer select:

SELECT  pvt.OrderStatus, 
        East = COALESCE(pvt.East, 0), 
        West = COALESCE(pvt.West, 0), 
        North = COALESCE(pvt.North, 0), 
        South = COALESCE(pvt.South, 0)
FROM    (   SELECT  os.OrderStatus, si.Region, [Count] = COUNT(si.ID)
            FROM    OrderStatus AS os
                    LEFT JOIN SaleInfo AS si
                        ON si.OrderStatus = b.OrderStatus
        ) AS t
        PIVOT
        (   MAX([Count])
            FOR Region IN ([East], [West], [North], [South])
        ) AS pvt;

I much prefer the first option though.

EDIT

Example showing 0 returned for non existent data when using COUNT:

SELECT  pvt.Stat, pvt.East, pvt.West, pvt.North, pvt.South
FROM    (VALUES
            ('Stat1', 'East', 1),
            ('Stat2', 'East', 2),
            ('Stat1', 'West', 3)
        ) t (Stat, Region, ID)
        PIVOT
        (   COUNT(ID)
            FOR Region IN ([East], [West], [North], [South])
        ) AS pvt;

Upvotes: 2

Related Questions