0
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?

  • 2
    Change your select * by select isnull([1],0), isnull([2],0), ... – McNets Apr 11 '18 at 10:41
1

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;
| improve this answer | |
  • 1
    I don't think he needs a dynamic query. Because there is a fixed number of pivot columns (month) dbfiddle.uk/… – McNets Apr 11 '18 at 11:05
  • @McNets - You are probably correct in this example - I just wanted to offer a dynamic solution to give the OP another option. I've updated my answer to include your valuable comment. Thanks. – Scott Hodgin Apr 11 '18 at 11:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.