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");
>

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.