For Creating Date Columns Dynamically

Satish,

Check if this helps:

create table #temp (id int identity(1,1),name varchar(100), date datetime)


insert #temp(name,date) select 'jk','2014/01/01'
insert #temp(name,date) select 'jk','2014/01/01'
insert #temp(name,date) select 'jk','2014/01/01'
insert #temp(name,date) select 'jay','2014/01/02'
insert #temp(name,date) select 'jay','2014/01/02'
insert #temp(name,date) select 'jk','2014/01/02'
insert #temp(name,date) select 'Kumaur','2014/01/01'
insert #temp(name,date) select 'Kumaur','2014/01/02'
insert #temp(name,date) select 'Kumaur','2014/01/03'
insert #temp(name,date) select 'jk','2014/01/03'
insert #temp(name,date) select 'jk','2014/02/03'
insert #temp(name,date) select 'jk','2014/02/03'
insert #temp(name,date) select 'Jay','2014/02/01'

--select * from #temp order by name

/*
--normal method using PIVOT
select *
from
(
        select id,name,date from #temp
) tab
PIVOT
(
        count(id) for date in([01/01/2014],[01/02/2014],[01/03/2014])
) pvt

*/
--Dynamic PiVOT to fit in the range of dates as per user input of @Year and @month - 1 for Jan, 2 for Feb and so on..
--Provides data for all the columns available in the table
declare @month int,@year int,@date_list nvarchar(max),@sql nvarchar(max)

--Give the Month input here
set @year=2014
set @month=1
set @date_list=''

select @date_list=@date_list+',['+isnull(convert(varchar,date,101),'')+']'
from (Select distinct date from #temp 
where datepart(mm,date)=@month and datepart(yyyy,date)=@year) tt


--set @date_list=stuff(@date_list,1,1,'')

set @sql='
select *
from
(
        select id,name,date from #temp
) tab
PIVOT
(
        count(id) for date in('+stuff(@date_list,1,1,'')+')
) pvt'

print @sql
exec sp_executesql @sql

---

Thanks,

Jay

<If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>