Why can't i do a crosstab query in sqlserver (1 Viewer)

[MsLady]

Okay! I've had it! Why is crosstab query a nightmare in sql server
This is absolutely ridiculous and i can't imagine why they left the feature out

Seeing as this is just a piece of cake in Access and it will run smoothly without pulling teeth but i am made to understand (by the powers that be) that i have to do some karate with sqlserver to get the same results. imagine that. If any sweet somebody can figure it out, please show me how!

TRANSFORM Count(Employees.MaritalStatus) AS MaritalStatusCount
SELECT Employees.MaritalStatus
FROM Employees INNER JOIN Offices ON Employees.OfficeId = Offices.officeId
WHERE ((Not (Employees.MaritalStatus) Is Null))
GROUP BY Employees.MaritalStatus
PIVOT Offices.officeLocation;

Thank you.

 

[pbaldy]

T-SQL does not support the Transform/Pivot syntax that Access uses. If you Google "crosstab t-sql" you'll find a number of discussions/solutions. Here's one:

http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

 

[Banana]

Would it be easier to use a sub-query to pull the dataset from SQL server then use that set to create a crosstab query locally?

 

[MsLady]

Would it be easier to use a sub-query to pull the dataset from SQL server then use that set to create a crosstab query locally?

well..how?

 

[Banana]

First, make a ODBC query that will gather all data you need for a crosstab query. Depending on how your data are stored, you may need just one or maybe more. Be sure to use a WHERE clause to filter the data as you need to.

Then make another query, and base it on the first query and make it a crosstab query.

I hope that makes sense...