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)
