Sql-server – How to get 0 in the null value fields

nullpivotsql serversum

select *
from 
( 
    select type,SUM(ISNULL(amount,0)) AS amount,month(date) as date from tbl_cashmaster
    where date  between '2016/12/31' and '2017/12/31' 
    group by type,month(date) 
) src
pivot
(
  sum(amount)
  for date in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) piv;

How can I get zero in null value columns? The above query is returning null's, where there is no data. What changes do I need to make in this to get 0 instead of null?

Best Answer

If you have a fixed number of pivot points, you could do what McNets suggests in the comments:

Change your select * by select isnull([1],0), isnull([2],0), ..

For a variable number of pivot points, you can use dynamic SQL - here is an example:

Note the references to colCoalesceNull

set nocount on
DECLARE @sql AS NVARCHAR(2000);
DECLARE @col AS NVARCHAR(2000);
DECLARE @colCoalesceNull AS NVARCHAR(2000);

IF OBJECT_ID('tempdb..#PaymentHistory') IS NOT NULL drop Table #PaymentHistory
CREATE TABLE #PaymentHistory (
    EmployeeName VARCHAR(100)
    ,DatePaid DATE
    ,TotalCheckAmt DECIMAL(11, 2)
    );
insert into #Paymenthistory values ('James','2016-01-01',100.00);
insert into #Paymenthistory values ('James','2016-02-01',100.00);
insert into #Paymenthistory values ('James','2016-03-01',100.00);
insert into #Paymenthistory values ('James','2016-04-01',100.00);
insert into #Paymenthistory values ('James','2016-05-01',100.00);
insert into #Paymenthistory values ('James','2016-06-01',100.00);
insert into #Paymenthistory values ('James','2016-07-01',100.00);
insert into #Paymenthistory values ('James','2017-01-01',100.00);
insert into #Paymenthistory values ('James','2018-10-01',900.00);

insert into #Paymenthistory values ('Roger','2016-01-01',100.00);
insert into #Paymenthistory values ('Roger','2016-02-01',100.00);
insert into #Paymenthistory values ('Roger','2016-03-01',100.00);
insert into #Paymenthistory values ('Roger','2016-04-01',100.00);
insert into #Paymenthistory values ('Roger','2016-05-01',100.00);
insert into #Paymenthistory values ('Roger','2016-06-01',100.00);
insert into #Paymenthistory values ('Roger','2016-07-01',100.00);
insert into #Paymenthistory values ('Roger','2020-10-01',900.00);
;
;
SELECT @col = 
        Coalesce(@col + ', ', '') + QUOTENAME(PvtColumnName)
    ,@colCoalesceNull = 
        Coalesce(@colCoalesceNull + ', ', '') + 'coalesce(' + QUOTENAME(PvtColumnName) + ',0) as ' + QUOTENAME(PvtColumnName)
FROM (
    SELECT DISTINCT 
    'Q' + 
    CONVERT(VARCHAR(1), DATEPART(Quarter, [DatePaid])) + 
    CONVERT(VARCHAR(4), year([DatePaid])) 
        AS PvtColumnName
    FROM #PaymentHistory
    ) AS PaymentHistory;

PRINT @col
PRINT @colcoalescenull

SET @sql = N'
with cte_PaymentHistory as
(
select 
''Q'' + CONVERT(varchar(1),DATEPART(Quarter, [DatePaid])) + CONVERT(varchar(4),year([DatePaid])) as PvtColumnName
,EmployeeName
,TotalCheckAmt
from #PaymentHistory
)
SELECT EmployeeName, ' + @colCoalesceNull + 'FROM cte_PaymentHistory
    PIVOT(SUM(TotalCheckAmt)
    FOR PvtColumnName IN (' + @col + ')) AS PivotPaymentHistory';

EXEC sp_executesql @sql;
Related Question