Posted by Plamen Ratchev on 01/23/07 17:20

What you need is table pivoting (normally done best in reporting tools). If
you have to do it in SQL, here are a couple options:

SQL Server 2005 only (using the PIVOT table operator, you can look it up in
SQL Server 2005 BOL):

SELECT M1.Region, M1.County, M1.Year,
SUM([Jan]) AS [Jan],
SUM([Feb]) AS [Feb],
SUM([Mar]) AS [Mar],
SUM([Apr]) AS [Apr],
SUM([May]) AS [May],
SUM([Jun]) AS [Jun],
SUM([Jul]) AS [Jul],
SUM([Aug]) AS [Aug],
SUM([Sep]) AS [Sep],
SUM([Oct]) AS [Oct],
SUM([Nov]) AS [Nov],
SUM([Dec]) AS [Dec]
FROM MyTable AS M1
INNER JOIN (SELECT Region, County, Year, Value, Month
FROM MyTable) AS M2
PIVOT ( SUM(Value) FOR Month IN ([Jan],
[Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov],
[Dec])) AS M3
ON M1.Region = M3.Region AND M1.County = M3.County AND
M1.Year = M3.Year
GROUP BY M1.Region, M1.County, M1.Year


SQL Server 2000 and SQL Server 2005:

SELECT Region, County, Year,
SUM(case when Month = 'Jan' then Value else 0 end) AS [Jan],
SUM(case when Month = 'Feb' then Value else 0 end) AS [Feb],
SUM(case when Month = 'Mar' then Value else 0 end) AS [Mar],
SUM(case when Month = 'Apr' then Value else 0 end) AS [Apr],
SUM(case when Month = 'May' then Value else 0 end) AS [May],
SUM(case when Month = 'Jun' then Value else 0 end) AS [Jun],
SUM(case when Month = 'Jul' then Value else 0 end) AS [Jul],
SUM(case when Month = 'Aug' then Value else 0 end) AS [Aug],
SUM(case when Month = 'Sep' then Value else 0 end) AS [Sep],
SUM(case when Month = 'Oct' then Value else 0 end) AS [Oct],
SUM(case when Month = 'Nov' then Value else 0 end) AS [Nov],
SUM(case when Month = 'Dec' then Value else 0 end) AS [Dec]
FROM MyTable
GROUP BY Region, County, Year


In both cases you have to replace the Jan, Feb, etc. with the correct
spelling of the months in your database.

Regards,

Plamen Ratchev
http://www.SQLStudio.com

[Back to original message]