---------------------------- Code--------------------------
create table Risks ( Project varchar(100),ItemID int,Created datetime)
Insert into Risks
values ('A',12,getdate()),
('A',10,getdate()-14),
('b',12,getdate()-30),
('b',11,getdate()-70),
('c',12,getdate()-50),
('c',10,getdate()-45)
declare @SQL nvarchar(max), @Cols nvarchar(max)
select @Cols = stuff(
((select ', ' + quotename(Project) from
(select distinct Project from Risks) X
ORDER BY Project for XML PATH(''),type).value('.','nvarchar(max)'))
,1,2,'')
set @SQL =
';with cte as (select Project, ItemID, LEFT(datename(month, Created),3) as [MonthName] from Risks)
select *, ' + replace(@Cols, '], [','] + [') + ' as Total
from cte PIVOT (count(ItemID) for Project IN (' + @cols +')) pvt'
print @SQL -- testing
execute(@SQL)
---------------------------- Code--------------------------
---------------------------- Result -------------------------
