Reply to Re: Help wanted msaccess PIVOT-query --> MS-SQL view/sp

Your name:

Reply:


Posted by Gert v O on 01/20/06 00:01

Hugo Kornelis wrote:
> On Wed, 18 Jan 2006 19:49:49 +0100, Gert v O wrote:
>
>> Can someone help me parsing this ms-access PIVOT sql-statement to a
>> ms-sql-server sql-statement?
>> Many thanks in advance
>>
>> TRANSFORM Count(KlantenStops.id) AS AantalVanid
>> SELECT KlantenStops.Uitvoerder, KlantenStops.Klant
>> FROM KlantenStops
>> GROUP BY KlantenStops.Uitvoerder, KlantenStops.Klant
>> PIVOT DatePart("m",leverdatum,1,0) In
>> ("1","2","3","4","5","6","7","8","9","10","11","12");
>>
>
> Hi Gert,
>
> I'm not exactly sure how the Access PIVOT syntax works (including
> table structure, sample data and expected results would have been a
> good idea; check www.aspfaq.com/2006 for the best format to supply
> this info), but the query below will do what I think the Access
> format does:
>
> SELECT Uitvoerder, Klant,
> COUNT(CASE WHEN DATEPART(month, leverdatum) = 1 THEN 'TelMe'
> END) AS Januari,
> COUNT(CASE WHEN DATEPART(month, leverdatum) = 2 THEN 'TelMe'
> END) AS Februari,
> .....,
> COUNT(CASE WHEN DATEPART(month, leverdatum) = 12 THEN 'TelMe'
> END) AS December
> FROM KlantenStops
> GROUP BY Uitvoerder, Klant
>
> If you're using SQL Server 2005, you can also use the new PIVOT
> syntax. You'll have to check Books Online for the details, though, as
> I haven't had a chance to play with the new syntax yet.


Thanx Hugo

[Back to original 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

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