Changing Access Crosstab to SQL Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Changing Access Crosstab to SQL Server

Author

Topic

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-24 : 04:59:16

I have a Access Crosstab Query which I need to recode into SQL Server, as Crosstabs are not a function in SQL Server how can I write this into SQL Server?

This is my Access code

TRANSFORM Count(PARAMED_TEMP_BUILD.EventID) AS CountOfEventID
SELECT PARAMED_TEMP_BUILD.ContactServiceID
FROM PARAMED_TEMP_BUILD
WHERE (((PARAMED_TEMP_BUILD.F2F)='y')
AND ((PARAMED_TEMP_BUILD.TH_SOR_LOC_CAT)='DA'
Or (PARAMED_TEMP_BUILD.TH_SOR_LOC_CAT)='NR'
Or (PARAMED_TEMP_BUILD.TH_SOR_LOC_CAT)='COM'))
GROUP BY PARAMED_TEMP_BUILD.ContactServiceID
ORDER BY PARAMED_TEMP_BUILD.ContactServiceID
PIVOT PARAMED_TEMP_BUILD.THE_MONTH;

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-24 : 05:02:45

See PIVOT function in Books Online.

E 12°55'05.63"
N 56°04'39.26"

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-24 : 06:00:12

I've been looking at this website below and tried the example within it and understood what its doing, I have then applied this my need and created this code below. My problem that I cant see how to get around is that it doesnt do what I thought it would do or do what the Access Query in Thread 1 does.

http://www.programmersheaven.com/2/SQL-server-2005-school-lesson-4-p2

My Code
SELECT ContactServiceID
FROM PARAMED_TEMP_BUILD
PIVOT (COUNT(EventID) FOR THE_MONTH IN ([MONTH])) AS PVT
WHERE ((F2F='y')
AND (TH_SOR_LOC_CAT='DA')
OR (TH_SOR_LOC_CAT='NR')
OR (TH_SOR_LOC_CAT='COM'))
GROUP BY ContactServiceID
ORDER BY ContactServiceID

Where have I gone astray?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 06:02:53

you should give values directly inside PIVOT rather column name. SO if you're trying to get values for each month give January,February,... inside.Alternatively post your reqd output with some sample data if you want us to tweak your query

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-24 : 06:22:57

This is sample data from the query in Access and how I want it to look through SQL Server

ContactServiceID,4,5,6,7,8,9
ADULT COMM MATRONS,656,596,677,641,588,461
CATS MSK,1002,819,928,951,748,1092
CHILDREN'S COMPLEX NEEDS,217,208,250,279,238,218
COMM CHILDREN'S NURS - ACUTE,,,3,8,124,192
COMM CHILDREN'S SERV - EPILEPSY,,1,9,19,8,16
COMM HEART FAILURE SERVICE,247,223,217,297,198,255
COMM RESPIRATORY SERVICE,53,44,34,45,8,43

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-24 : 06:42:55

[code]SELECT ContactServiceID,
SUM(CASE WHEN THE_MONTH = 01 THEN 1 ELSE 0 END) AS January,
SUM(CASE WHEN THE_MONTH = 02 THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN THE_MONTH = 03 THEN 1 ELSE 0 END) AS March,
SUM(CASE WHEN THE_MONTH = 04 THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN THE_MONTH = 05 THEN 1 ELSE 0 END) AS May,
SUM(CASE WHEN THE_MONTH = 06 THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN THE_MONTH = 07 THEN 1 ELSE 0 END) AS July,
SUM(CASE WHEN THE_MONTH = 08 THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN THE_MONTH = 09 THEN 1 ELSE 0 END) AS September,
SUM(CASE WHEN THE_MONTH = 10 THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN THE_MONTH = 11 THEN 1 ELSE 0 END) AS November,
SUM(CASE WHEN THE_MONTH = 12 THEN 1 ELSE 0 END) AS December
FROM PARAMED_TEMP_BUILD
WHERE F2F = 'y'
AND TH_SOR_LOC_CAT IN ('DA', 'NR', 'COM')
GROUP BY ContactServiceID
ORDER BY ContactServiceID[/code]

E 12°55'05.63"
N 56°04'39.26"

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-24 : 07:22:57

Excellent thanks for that :-)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-24 : 08:05:51

Your welcome.
I figured you didn't take the advice to read Books Online so this is another approach.

E 12°55'05.63"
N 56°04'39.26"

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-24 : 08:21:33

Where would I find these books online?

I just googled PIVOT function in SQL Server and was reading the website in thread Posted - 10/24/2008 : 06:00:12

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-24 : 09:30:02

If you are using SSMS 2005 (SQL Server Management Studio),
just type PIVOT in query window and press shift+f1.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources