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

• Next in forum: Re: Turning Rows into Columns
• Prev in forum: Re: Turning Rows into Columns
• Thread view: Re: Turning Rows into Columns

[Reply to this message]