Pivot is really useful in SQL 2005

Suppose I have a table [tblBud] like below and want to get a [budAmount] summary table where rows are [budAcode] and columns are [budMnth]. (Sorry for the bad identifiers, not under my control)

[budID]    [budMnth]    [budAcode]    [budAmount]
1893    2008-09-01 00:00:00.000    A532    2083.33
1894    2008-10-01 00:00:00.000    A532    2083.33
1895    2008-11-01 00:00:00.000    A532    2083.33
1896    2008-12-01 00:00:00.000    A532    2083.33
1897    2008-01-01 00:00:00.000    A533    12500.00
1898    2008-02-01 00:00:00.000    A533    12500.00
1899    2008-03-01 00:00:00.000    A533    12500.00
1900    2008-04-01 00:00:00.000    A533    12500.00
1901    2008-05-01 00:00:00.000    A533    12500.00
1937    2008-05-01 00:00:00.000    A536    15000.00
.......


The solution is to use pivot. (Today I tried it for the first time.)

Essentially there are four steps

-- 1, Set @Year as parameter

DECLARE @Year VARCHAR(5)

SET @Year = '2009'  -- This can be input from outside

DECLARE @range VARCHAR(MAX)

SET @range = ''

DECLARE @query VARCHAR(MAX)

SET @query = ''

-- 2, @range will be used in pivot clause

SELECT @range=@range+'['+ CONVERT(VARCHAR, YEAR(budmnth)*100 + MONTH(budmnth)) +'],' FROM tblbud

WHERE YEAR(budmnth) = @Year

GROUP BY YEAR(budmnth)*100 + MONTH(budmnth)

-- remove the last comma

SET @range =LEFT(@range, LEN(@range)-1)

PRINT @range

--3, Set up @query to be executed

SET @query = '

SELECT *

FROM (

    SELECT BudACode,

    YEAR(BudMnth)*100 + Month(BudMnth) as BudMnth,

    CONVERT(Float,COALESCE(BudAmount,0)) as BudAmount

    FROM tblbud

    ) as row

PIVOT (SUM(budAmount) FOR budMnth IN (' + @range + ')) as col'

PRINT @query

-- 4, Run

EXECUTE (@query)

Reference
 http://geekswithblogs.net/lorint/archive/2006/08/04/87166.aspx