Help wanted msaccess PIVOT-query --> MS-SQL view/sp

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

--
Hugo Kornelis, SQL Server MVP|||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|||I have a blog post on using dynamic SQL to pivot when you don't know how many columns there will be it's here http://www.daymap.net/blog