use of PIVOT operator dynamically

Yes,

Both the queries worked well. But there are two issues with the resultset.

1. Order by should be proper like January-2013, February-2013......December-2013. Currently order is April-2013, February-2013

2. I want NULL in the resultset to be shown as zero (0)

Please help with this

Hi SwadPune,

Please refer to the following codes:

create table #temp(dtDate datetime,Category  varchar(10),Amount decimal(10,2),Notes varchar(20))
insert into #temp values('2013-01-15 00:00:00.000','Cat1',10.00,NULL)
insert into #temp values('2013-01-16 00:00:00.000','Cat2',25.00,NULL)
insert into #temp values('2013-02-15 00:00:00.000','Cat1',25.00,NULL)
insert into #temp values('2013-02-17 00:00:00.000','Cat3',14.00,NULL)
insert into #temp values('2013-03-10 00:00:00.000','Cat2',10.00,NULL)
insert into #temp values('2013-03-02 00:00:00.000','Cat4',120.00,NULL)
insert into #temp values('2013-04-20 00:00:00.000','Cat5',60.00,NULL)
insert into #temp values('2013-04-29 00:00:00.000','Cat2',10.00,NULL)
insert into #temp values('2013-05-05 00:00:00.000','Cat3',15.00,NULL)
insert into #temp values('2013-05-21 00:00:00.000','Cat4',20.00,NULL )
-------------------
DECLARE
    @cols nvarchar(max),
    @stmt nvarchar(max),
        @colsF nvarchar(max)
        
select @cols = STUFF((
SELECT ','+[MonthName]+ '_' + cast([year] as varchar(4))  from
(
select month(dtDate) [month],year(dtDate) [year],DATENAME(m,dtDate) as [MonthName]
from #temp
group by year(dtDate) ,month(dtDate),DATENAME(m,dtDate)
)as t
order by [year],[month]
for xml path('')),1,1,'')
        
select @colsF = STUFF((
SELECT ', case when '+[MonthName]+ '_' + cast([year] as varchar(4))+' is null then ''0'' else '+[MonthName]+ '_' + cast([year] as varchar(4)) + +' end as ' +convert(varchar(max),[MonthName]+ '_' + cast([year] as varchar(4))) from
(
select month(dtDate) [month],year(dtDate) [year],DATENAME(m,dtDate) as [MonthName]
from #temp
group by year(dtDate) ,month(dtDate),DATENAME(m,dtDate)
)as t
order by [year],[month]
for xml path('')),1,1,'')

SELECT @stmt = 'SELECT Category,'+@colsF+'
    FROM (select Category, [Amount],datename(m,dtDate) + ''_'' + cast(year(dtDate) as varchar(4)) MonthYear from #temp) as T
        PIVOT 
        (
            Sum(Amount) For MonthYear In  (' + @cols + ')
        ) as P'
exec sp_executesql  @stmt = @stmt

Best Regards,

Allen Li

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.

Thanks for helping make community forums a great place.