|
Home > Archive > Microsoft SQL Server forum > January 2006 > Help wanted msaccess PIVOT-query --> MS-SQL view/sp
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Help wanted msaccess PIVOT-query --> MS-SQL view/sp
|
|
| Gert v O 2006-01-18, 1:23 pm |
| 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 2006-01-19, 3:24 am |
| 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
| |
| Gert v O 2006-01-19, 8:24 pm |
| Hugo Kornelis wrote:
> On Wed, 18 Jan 2006 19:49:49 +0100, Gert v O wrote:
>
>
> 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
|
|
|
|
|