Crosstab Queries

A crosstab query summarizes tabular data and places it into a columnar format. In a crosstab query, matching values can be counted, summed, averaged, compared to other values, and so on.

Suppose you want to know the average salary paid for various employee classifications in different countries. One way to do this is to create a standard SELECT query that uses the GROUP BY clause for the Country and Title fields, and averages the values in the Salary field:

SELECT 
	Employees.Country, 
	Employees.Title, 
	Avg(Employees.Salary) AS AvgOfSalary
FROM Employees
GROUP BY Employees.Country, Employees.Title;

This query results in the following table.

It’s difficult, however, to make certain kinds of comparisons. The Country field information is repeated for each row, and in order to compare the average salary for various titles across countries, you must match numbers that are physically distant from one another on the page. You may easily overlook the fact that no sales manager is listed for the United States.

A crosstab query solves this problem by organizing the data so that the summarized information is placed in contiguous columns across the page. The following crosstab query produces the same information as the first query, but in a much more readable format:

TRANSFORM Avg(Employees.Salary) AS AvgOfSalary
SELECT Employees.Title
FROM Employees
GROUP BY Employees.Title
PIVOT Employees.Country;

The contents of the Country field become column headings. The Title field (specified in the SELECT clause) becomes the row heading and the values are the average of the Salary field.

To support crosstab operations, Microsoft Jet includes these two extensions to the standard SQL language:

• The TRANSFORM statement. Specifies that the query is a crosstab query. The first statement following the TRANSFORM statement must be an aggregate function, such as Avg, Sum, Min, Max, or Count, which summarizes the selected data.
  
  
• The PIVOT clause. Specifies the field or expression used to create the column headings in the query’s result set.

By default, Microsoft Jet presents the summarized columns in alphabetical order from left to right. Missing values are returned as Null. In the previous example, because no sales managers exist in the United States, there is no salary to average. The value in the USA column for Sales Manager is Null.

Column headings are based on the actual values contained in the field specified by the PIVOT clause. If values are missing, no column is created. For example, if your company has offices in Germany, but no employees were selected whose country is Germany, no column would be created for Germany.

You can force Microsoft Jet to create columns in an order other than alphabetical and force the creation of columns even if there is no data for that column value by using the optional IN clause with the PIVOT clause. The previous crosstab query can be revised as follows:

TRANSFORM Avg(Employees.Salary) AS AvgOfSalary 
SELECT Employees.Title 
FROM Employees 
GROUP BY Employees.Title 
PIVOT Employees.Country IN ('UK','Germany','France','USA');

This query results in the following table.

The target of the SELECT statement used to create the column headings can be an expression as well as an actual field name. A common use of this feature is to create groupings that don’t exist in the data. For example, you can group all orders into month groupings in the following manner:

TRANSFORM Count(*) 
SELECT Orders.EmployeeID
FROM Orders
GROUP BY Orders.EmployeeID
PIVOT Format(OrderDate,'mmm');

The following table illustrates a portion of the columns and rows returned by this query.

The column headings in this query are created with the Format function, which turns the value of the OrderDate field into a three-letter month abbreviation. All orders within a given month (even if they were placed in different years) are summarized together.

Because the IN clause wasn’t used with the PIVOT clause, the months are displayed alphabetically. A more complete query, which combines a WHERE clause to limit the selection to orders within a given year and displays the columns in calendar order, is as follows:

TRANSFORM Count(*) 
SELECT Orders.EmployeeID 
FROM Orders 
WHERE Orders.OrderDate Between #1/1/96# And #12/31/96# 
GROUP BY Orders.EmployeeID 
PIVOT Format(OrderDate,'mmm') IN ('Jan','Feb','Mar','Apr',
	'May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');

The following table shows a portion of the result from this query.