use of PIVOT operator dynamically

Try the below:

create Table Test_30Oct1(dtDate Date,Category varchar(10),Amount money,Notes varchar(50))
Insert into  Test_30Oct1 Values
('2013-01-15 00:00:00.000','Cat1',10.00,NULL),
('2013-01-16 00:00:00.000','Cat2',25.00,NULL),
('2013-02-15 00:00:00.000','Cat1',25.00,NULL),
('2013-02-17 00:00:00.000','Cat3',14.00,NULL),
('2013-03-10 00:00:00.000','Cat2',10.00,NULL),
('2013-03-02 00:00:00.000','Cat4',120.00,NULL),
('2013-04-20 00:00:00.000','Cat5',60.00,NULL),
('2013-04-29 00:00:00.000','Cat2',10.00,NULL),
('2013-05-05 00:00:00.000','Cat3',15.00,NULL),
('2013-05-21 00:00:00.000','Cat4',20.00,NULL )

 DECLARE @query VARCHAR(4000) 
 DECLARE @Categories VARCHAR(2000) 
 SELECT  @Categories = STUFF(( SELECT DISTINCT                        
 '],[' + Datename(Month,dtDate) + '-' + Cast(Datepart(year,dtDate) as varchar(4))
 FROM    (Select distinct dtdate From Test_30Oct1) A
 ORDER BY '],[' + Datename(Month,dtDate) + '-' + Cast(Datepart(year,dtDate) as varchar(4))
 FOR XML PATH('')                         ), 1, 2, '') + ']' 
 
  
 SET @query = 'SELECT * FROM ( SELECT Datename(Month,dtDate) + ''-'' + Cast(Datepart(year,dtDate) as varchar(4)) dt
 ,amount ,Category    
 FROM Test_30Oct1 )t PIVOT (SUM(amount) FOR dt IN ('+@Categories+')) AS pvt' 
 --Print @Query
 EXECUTE (@query) 
 
Drop table Test_30Oct1

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.