Pivot example

DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)
SELECT  @listCol = STUFF(( SELECT DISTINCT
                                '],[' + ltrim(str(YEAR(transactiondate)))
                        FROM   TblBillingTransaction
                        ORDER BY '],[' + ltrim(str(YEAR(transactiondate)))
                        FOR XML PATH('')
                                    ), 1, 2, '') + ']'
print @listCol
SET @query =

'select * from(select scanamount, datepart(m,transactiondate) as Month ,year(transactiondate) as transactiondate  from TblBillingTransaction where Obsolete=0 and BillingId!=0)as v
pivot (sum(scanamount) for transactiondate
in('+@listCol+') ) as PVT order by 1 asc'


EXECUTE (@query)

RESULT

Month	2010	2011
1	1678000	8983115
2	4612000	11186996
3	5699800	10595608
4	5960000	9839065
5	6264010	9913435
6	7591395	11367465
7	9239360	12233055
8	7814870	11166010
9	8532125	11613364
10	8831590	12208845
11	8928700	7786655
12	10024105	NULL