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

[Gert v O]

Can someone help me parsing this ms-access PIVOT sql-statement to a
ms-sql-server sql-statement?
Many thanks in advance

TRANSFORM Count(KlantenSt ops.id) AS AantalVanid
SELECT KlantenStops.Ui tvoerder, KlantenStops.Kl ant
FROM KlantenStops
GROUP BY KlantenStops.Ui tvoerder, KlantenStops.Kl ant
PIVOT DatePart("m",le verdatum,1,0) In
("1","2","3","4 ","5","6","7"," 8","9","10","11 ","12");

[Hugo Kornelis]

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(KlantenSt ops.id) AS AantalVanid
SELECT KlantenStops.Ui tvoerder, KlantenStops.Kl ant
FROM KlantenStops
GROUP BY KlantenStops.Ui tvoerder, KlantenStops.Kl ant
PIVOT DatePart("m",le verdatum,1,0) In
("1","2","3"," 4","5","6","7", "8","9","10","1 1","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

[Gert v O]

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(KlantenSt ops.id) AS AantalVanid
SELECT KlantenStops.Ui tvoerder, KlantenStops.Kl ant
FROM KlantenStops
GROUP BY KlantenStops.Ui tvoerder, KlantenStops.Kl ant
PIVOT DatePart("m",le verdatum,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

[floot]

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