Dynamic Columns in SQL Query - URGENT PLZZ HELP ME OUT

hi,

Ok cool....

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'
 
 Declare @SumClearcolumnscsv varchar(max)
 Select @SumClearcolumnscsv = COALESCE(@SumClearcolumnscsv + '+ isnull([','') + Datename(m,Date) + cast(year(Date) as varchar) + 'Clear],0) '
 from #temp
 group by Date,year(Date)
 
 --print 'isnull([' +@SumClearcolumnscsv
 
 Declare @SumRejectcolumnscsv varchar(max)
 Select @SumRejectcolumnscsv = COALESCE(@SumRejectcolumnscsv + ' + isnull([','') + Datename(m,Date) + cast(year(Date) as varchar) + 'Rejected],0) '
 from #temp
 group by Date,year(Date)
 
 --print @SumRejectcolumnscsv
 
SET @sql = '
SELECT Company, Location,Status,sum([' + @Sumcolumnscsv + ',
SUM(Total) as Total,
SUM(ClearTotal) as ClearTotal,
SUM(RejectTotal) as RejectTotal from (
SELECT Company, Location,Status, [' + @columnscsv + '] , isnull([' + @Sumcolumnscsvho + '],0) as Total
,isnull([' +@SumClearcolumnscsv + ' as ClearTotal,
isnull([' +@SumRejectcolumnscsv + ' as RejectTotal
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
) A
GROUP BY
  ROLLUP((Company, Location,Status,' + '[' + @columnscsv + ']' + ', [Total] ))'

exec(@sql)

hope this will help you.
do not forgot to mark it as answer if helped.