pivot convert null to zero

Question

Below i have create a 2 table transaction_type and transaction_master then transaction_type. transaction_type is contain the credit and debit id's then transaction_type is contain the transaction details

   transaction_type
   -----------------
   transaction-id|transaction-name
   1             |credit 
   2             |debit

   transaction_master
   -----------
   transaction-id|date_of_transaction|amount
   1             |2016-01-12         |100  
   2             |2015-12-30         |200
   1             |2016-01-05         |300   
   1             |2015-12-04         |500 
   2             |2015-12-12         |50  
   2             |2015-12-25         |1000    
   1             |2016-01-30         |100    

   normal PIVOT output is
   -----------------------
   YEAR|Jan |Feb |mar |Apr |may |Jun |Jul |Aug |Sep |Oct |Nov |Dec

   2015|null|null|null|null|null|null|null|null|null|null|null|-700

   2016|500 |null|null|null|null|null|null|null|null|null|null|null


   But i want this
   YEAR|Jan |Feb |mar |Apr |may |Jun |Jul |Aug |Sep |Oct |Nov |Dec

   2015|0   |0   |0   |0   |0   |0   |0   |0   |0   |0   |0   |0   

   2016|500 |0   |0   |0   |0   |0   |0   |0   |0   |0   |0   |0  

in above "NULL" and "negative" values should be the "0"

   with AA as(select year(d.date_of_transaction),left(date name(month,date_of_transaction),3) as month,sum (case when c. transaction_type like '%c%' then d.Amount else d.Amount*-1)as balance
 from transaction_master d join on transaction_type c c.transaction-id=d.transaction-id group by date_of_transaction) select * from AA pivot(sum(balance) for [month] in (Jan,Feb,Mar,Apr,May,jun,Jul,Aug,Sep,Oct,Nov,Dec)) as pvt

Answer 1

You can use CASE and ISNULL in following:

with aa as(select year(d.date_of_transaction), 
                  case when isnull(left(datename(month,date_of_transaction),3),0) < 1 then 0 else left(datename(month,date_of_transaction),3) end as month