Prosperity
Prosperity

Reputation: 33

SQL Replacing a Null value with 0 within a Pivot

I have this query,

Select * From 
(Select 
    S.EmployeeID As [Employee ID],
    E.Name,
    YEAR([Date]) As [Year],
    DATENAME(MONTH, [Date]) As [Month], 
    IsNull(SUM(Amount), 0) As TotalAmount
    From Sales S Left Outer Join Employee E
    On S.EmployeeID = E.EmployeeID
    Group By 
    S.EmployeeID,
    E.Name, 
    YEAR([Date]),
    DATENAME(MONTH, [Date])) As MonthlySale
Pivot(SUM(TotalAmount)   
    For Month In ([January],[February],[March],[April],[May],
                  [June],[July],[August],[September],[October],
                  [November],[December])) As MyPivot

Which returns this table (Example Table),

+--+---------+-----+------+-------+
|ID|  Name   |Year |Jan   |Feb    | Exc
+--+---------+-----+------+-------+
|1 |John Doe |2014 |Null  |Null   | Exc
+--+---------+-----+------+-------+
|2 |Jane Doe |2014 |Null  |Null   | Exc
+--+---------+-----+------+-------+

How can I change those null values to 0s, I tried IsNull but to no avial, any ideas?

Upvotes: 1

Views: 91

Answers (2)

Prosperity
Prosperity

Reputation: 33

Ended up using,

Select [Employee ID], Name, @Year As [Year],
IsNull(January, 0)  As January,  IsNull(February, 0)  As February,  IsNull(March, 0)      As March,
IsNull(April, 0)    As April,    IsNull(May, 0)       As May,       IsNull(June, 0)       As June,
IsNull(July, 0)     As July,     IsNull(August, 0)    As August,    IsNull(September, 0)  As September,
IsNull(October, 0)  As October,  IsNull(November, 0)  As November,  IsNull(December, 0)   As December
From 

Upvotes: 1

sdrzymala
sdrzymala

Reputation: 387

with cte as 
(

Select * From 
(Select 
    S.EmployeeID As [Employee ID],
    E.Name,
    YEAR([Date]) As [Year],
    DATENAME(MONTH, [Date]) As [Month], 
    IsNull(SUM(Amount), 0) As TotalAmount
    From Sales S Left Outer Join Employee E
    On S.EmployeeID = E.EmployeeID
    Group By 
    S.EmployeeID,
    E.Name, 
    YEAR([Date]),
    DATENAME(MONTH, [Date])) As MonthlySale
Pivot(SUM(TotalAmount)   
    For Month In ([January],[February],[March],[April],[May],
                  [June],[July],[August],[September],[October],
                  [November],[December])) As MyPivot

)
select *, coalesce(Januar, 0) from cte

Upvotes: 0

Related Questions