Creating a Crosstab Query with SQL TRANSFORM

 < Day Day Up > 

Creating a Crosstab Query with SQL TRANSFORM

A crosstab query is a great way to present a lot of data in a consistent and easy-to-read format. These queries summarize data by categories, generally using an aggregate function for the summary calculations. The general rule of thumb is that the query must have at least one column heading and one summary column.

Crosstab queries are produced by SQL's TRANSFORM statement, which takes the form


 TRANSFORM aggregate SELECT statement PIVOT column 

where aggregate is one of many aggregate functions used to evaluate a column, statement is any valid SQL statement, and column identifies the column heading(s). TRANSFORM doesn't support the SQL HAVING clause.


Crosstabs can be a bit intimidating, but there's an easy way to get the results you want:

  1. Start with a grouped SELECT statement using an aggregate function to evaluate the grouped column.

  2. Insert a TRANSFORM clause before the SELECT statement and move the aggregate function from the SELECT clause to the TRANSFORM clause.

  3. Follow the SELECT clause with a PIVOT clause and move a column from the GROUP BY clause to the new PIVOT clause.

There's no guarantee the results will be exactly what you want, but they will probably be very close. After you have the basic structure, you can more easily add column headings and column-evaluating aggregate functions.

The following statement presents a lot of information:


 TRANSFORM Sum([HourlyRate]*[Hours]) AS Total SELECT Clients.Client, Projects.ProjectName FROM Clients INNER JOIN Projects ON Clients.ClientID = Projects.ClientID  INNER JOIN Tasks ON Projects.ProjectID = Tasks.ProjectID  INNER JOIN Timeslips ON Tasks.TaskID = Timeslips.TaskID GROUP BY Clients.Client, Projects.ProjectName PIVOT Projects.StartDate 

The results show a list of clients and projects with a total of labor hours listed by columns according to start dates. In other words, you can quickly see how much money has been spent thus far on each project for each client, while quickly discerning when the project began. Because this query retrieves data from more than one table, the references include table and column names, which prevent ambiguous references, such as the ClientID column in the Clients and Projects tables.

     < Day Day Up > 

    Automating Microsoft Access with VBA
    Automating Microsoft Access with VBA
    ISBN: 0789732440
    EAN: 2147483647
    Year: 2003
    Pages: 186 © 2008-2017.
    If you may any questions please contact us: