You are here: Re: Turning Rows into Columns « MsSQL Server « IT news, forums, messages
Re: Turning Rows into Columns

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

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация