Search This Blog & Web

Tuesday, May 15, 2012

Dynamic Pivot query to return max number of records


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




No comments: