Need Help with Crosstab Queries

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 2000 Forums

SQL Server Development (2000)

Need Help with Crosstab Queries

Author

Topic

ddz
Starting Member

4 Posts

Posted - 2004-10-29 : 10:03:05

I'm a database newbie and I need to create queries (I think crosstabs) using SQL Server. One query needs to take a count and another needs to take a sum. I've never done these type of queries before, so I tried doing them first in Access using the wizard. The queries work just fine in Access. So I copied the SQL code that was generated in Access and pasted it as a stored procedure in SQL Server. But I'm running into several problems that I don't know how to fix.

Here's what my table (ContactMethods) looks like:
(I used dashes so that the data would line up under the column.)

ID- ContactHelpDesk
1 - email
2 - walk
3 - form
4 - phone
5 - email
6 - walk
7 - form

My ultimate goal is to be able to find out the total number of people who selected email, walk, form, and phone (in the ContactHelpDesk field). I also need to have email, walk, form, and phone to be column headings. I need it in this format so that I can take a sum for each column and refer to the column by name when using ColdFusion (a scripting language).

My first query (called ContactHelpDeskCrosstab) does a count:

TRANSFORM Count(ContactMethods.ContactStudent) AS CountOfContactStudent
SELECT ContactMethods.ID, Count(ContactMethods.ContactStudent) AS [Total Of ContactStudent]
FROM ContactMethods
GROUP BY ContactMethods.ID
PIVOT ContactMethods.ContactHelpDesk;

The result looks like this:

ID- Total of ContactStudent- email- form- phone- walk
1 - 1 ---------------------- 1
2 - 1 ------------------------------------------ 1
3 - 1 ----------------------------- 1
4 - 1 ----------------------------------- 1
5 - 1 ---------------------- 1
6 - 1 ------------------------------------------ 1
7 - 1 ----------------------------- 1

This is exactly what I need so that I can take a sum.

Next, I take a sum for each column (email, form, phone, walk). Here's the query that takes the sum:

SELECT DISTINCTROW Sum(ContactHelpDeskCrosstab.email) AS EmailTotal, Sum(ContactHelpDeskCrosstab.form) AS FormTotal, Sum(ContactHelpDeskCrosstab.phone) AS PhoneTotal, Sum(ContactHelpDeskCrosstab.walk) AS WalkTotal
FROM ContactHelpDeskCrosstab;

The result looks like this:

EmailTotal- FormTotal- PhoneTotal- WalkTotal
2 --------- 2 -------- 1 ---------- 2

This is exactly what I need. Now that it is in this format, I will be able to use ColdFusion to print out the total for each answer.

However, when I copy the SQL code generated in Access from the first query and paste it to a new stored procedure, I receive two error messages from SQL Server:
*Incorrect syntax near TRANSFORM.
*Incorrect syntax near PIVOT.

When I copy the SQL gode generated in Access from the second query and paste it to a new stored procedure, I receive this error message from SQL Server:

Incorrect syntax near ContactHelpDeskCrosstab. (I'm not sure if something is wrong with the syntax or if it's throwing that message because it can't find a query by that name. As I mentioned, it won't let me create that query because of the syntax problem.)

I'd appreciate any help with how I can fix these queries so that they will work with SQL Server. Thanks.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-29 : 10:12:29

All you need to do is use case statements


Select
	emailTotal = sum(case when ContactHelpDesk='email' then 1 else 0 end),
	formTotal = sum(case when ContactHelpDesk='form' then 1 else 0 end),
	phoneTotal = sum(case when ContactHelpDesk='phone' then 1 else 0 end),
	walkTotal = sum(case when ContactHelpDesk='walk' then 1 else 0 end),
--just in case
	otherTotal = sum(case when ContactHelpDesk not in ('email','form','phone','walk') then 1 else 0 end)
From ContactMethods

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources