Posted by markjerz@googlemail.com on 01/23/07 17:38

Thanks Plamen, much appreciated. I'm using SQL Server 2000 for this
project but for future knowledge which is the best to use (in this
case) speed/efficiency wise?

Thanks again


Plamen Ratchev wrote:

> 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]