Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2005

    Unanswered: Access crosstab -> SQL crosstab

    Hi all
    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
    SELECT tProjContacts.ProjectID
    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
    PIVOT tCompanyType.CoType;

  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    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.

    blindman "sqlblindman"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts