Posted by Hugo Kornelis on 01/18/06 22:38

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

[Back to original message]