Converting NULLs to 0 in SQL Pivot

The following code successfully generates a Pivot (shown below the code)

select * 
from (
Select GLAccount , DC , Amount  from AccountingTxns a where Acctno = '178523'
pivot (
sum (Amount)
for DC in ([D],[C])
as SumAmountDC
GLAccount             D       C
InterestDue     3801.37 3731.68
Cash            25600   25000
IntReceivable   3801.37 3801.37
IntIncome       NULL    3684.47
IntOverdue      NULL    116.9
Principal      25000    21868.32

How do i get rid of the NULLs (i.e. convert them to 0’s)?

  • SQL Server 2014 - Parsing XML with Cyrillic Characters
  • SQL Server: How do you remove punctuation from a field?
  • Writing set based SQL query without set operations (EXCEPT, INTERSECT)
  • Updating dimension tables using SQL Server (BIDs or Data Tools)
  • Select Values From SP And Temporary Tables
  • Unboxing an untyped field in a DataRow Without a Casting Error
  • Sql server ignoring numerator as per denominator
  • How output data from database to table?
  • Sum hierarchical values in table for Continent, countries and states
  • Supposedly easy trigger
  • How do I connect to an older sql database using LocalDb in VS 2012?
  • Query with multiple line results in only one line
  • One Solution collect form web for “Converting NULLs to 0 in SQL Pivot”

    use COALESCE,

    SELECT  GLAccount, 
            COALESCE(D, 0) D, 
            COALESCE(C, 0) C
                SELECT  GLAccount, 
                        DC , 
                FROM    AccountingTxns a 
                WHERE   Acctno = '178523'
            ) TableData
                FOR DC IN ([D],[C])
            ) SumAmountDC
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.