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

Oops, no need to SUM when using PIVOT, it should be like this:

SELECT DISTINCT M1.Region, M1.County, M1.Year,
[Jan],
[Feb],
[Mar],
[Apr],
[May],
[Jun],
[Jul],
[Aug],
[Sep],
[Oct],
[Nov],
[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
ORDER BY M1.Region, M1.County, M1.Year

Regards,

Plamen Ratchev
http://www.SQLStudio.com

[Back to original message]