Dynamic Columns in SQL Query

hi,

try...

Create table #temp
(ID int,
Company  varchar(25),
Location  varchar(25),
Date date,
Count int,
Status varchar(25))

insert into #temp values(1,'TCS','Mumbai','1-1-2010',1,'Clear'),
 (2,'Zensar','Hydrabad','2-2-2010',2,'Rejected'),
 (3,'Infosys','Pune','3-3-2011',4,'Clear'),
 (4,'IBM','Pune','4-4-2011',3,'Clear'),
 (5,'Microsoft','Singapore','5-5-2012',5,'Rejected'),
 (6,'Google','US','6-6-2013',4,'Rejected')
 
 declare @columnscsv varchar(MAX)
 declare @Sumcolumnscsv varchar(MAX)
 declare @Sumcolumnscsvho varchar(MAX)
 DECLARE @sql varchar(MAX)
 Select @columnscsv = COALESCE(@columnscsv + '],[','') + Datename(m,Date) + cast(year(Date) as varchar) + 'Clear],[' + Datename(m,Date) + cast(year(Date) as varchar) + 'Rejected' 
 from #temp
 group by Date,year(Date)
 
 Select @Sumcolumnscsvho = COALESCE(@Sumcolumnscsvho + '],0) + isnull([','') + Datename(m,Date) + cast(year(Date) as varchar) + 'Clear],0) + isnull([' + Datename(m,Date) + cast(year(Date) as varchar) + 'Rejected' 
 from #temp
 group by Date,year(Date)
 
 Select @Sumcolumnscsv = COALESCE(@Sumcolumnscsv + ',Sum([','') + Datename(m,Date) + cast(year(Date) as varchar) + 'Clear]) as ' + Datename(m,Date) + cast(year(Date) as varchar) + 'Clear,Sum([' + Datename(m,Date) + cast(year(Date) as varchar) + 'Rejected]) as ' + Datename(m,Date) + cast(year(Date) as varchar) + 'Rejected' 
 from #temp
 group by Date,year(Date)
 
 print 'isnull([' + @Sumcolumnscsvho + '],0) as Total'
 

SET @sql = '
SELECT Company, Location,Status,sum([' + @Sumcolumnscsv +', isnull([' + @Sumcolumnscsvho + '],0) as Total
FROM (
  select Company,Location, Datename(m,Date) + cast(year(Date) as varchar) + Status as m,Count, Status from #temp
) as A
PIVOT
(
    SUM(Count)
    FOR m IN (' + '[' + @columnscsv + ']' + ')
)AS pivot1 GROUP BY
  ROLLUP((Company, Location,Status,' + '[' + @columnscsv + ']' + '))'

--print @sql
exec(@sql)

hope this will help you.