I'm transferring some Access queries to SQL server and the crosstabs don't want to work, can anyone shed any light on the query below.
TRANSFORM Min(tCompany.cCompanyName) AS CompanyName
FROM tCompany INNER JOIN (tProjContacts INNER JOIN tCompanyType ON tProjContacts.CoTypeId = tCompanyType.CoTypeId) ON tCompany.CompanyID = tProjContacts.CompanyID
WHERE (((tCompanyType.CoType) Like "*topo*" Or (tCompanyType.CoType) Like "*ground*"))
GROUP BY tProjContacts.ProjectID
SQL Server 2000 does not have built-in crosstab functionality. Crosstab queries can be constructed using the CASE statement, if you follow the example you will find under CROSSTAB in Books Online. This will now produce dynamic crosstabs as in Access, however, as you must predefine your column names/groups.
In general, crosstabs should not be performed on the back-end server anyway. Nine times out of ten crosstabbing is a presentation issue, and is best handled by your interface or reporting application. The main reason Access has this built in is that Access incorporates an interface and reporting engine utility with the database. Interfaces and reports like to have consistent recordset formats for input. Try creating an Access report off of a dynamic Access crosstab and you will see the problems that crop up when you let the database do the cross-tabbing.
If it's not practically useful, then it's practically useless.