What is a CrossTab Query?

A cross tab query is a transformation of rows of data to columns. It usually involves aggregation of data e.g. totals broken down by months, products etc., where the months are represented by columns. It's very hard to visualize without an example, so we will provide one below.

Lets say you have a table of transactions that looks like this

customer_id	amount	purchase_date
123	41.50	2004-10-01
12	30.50	2004-09-01
12	40.50	2004-08-15

One example of a cross-tab is to take the above data and show how much you earned in each month across. This allows for easy comparison of profits from quarter to quarter. You want your resulting data to look like the below.

m2004_08	m2004_09	m2004_10	Total
40.50	30.50	41.50	112.50

How you get to the above representation varies from Database to Database. In the first example, we'll show you how to get there with standard ANSI-SQL tricks.

Standard ANSI-SQL Crosstab

SELECT 
    SUM(CASE WHEN purchase_date BETWEEN '2004-08-01' and   '2004-08-31' THEN amount ELSE 0 END) As m2004_08, 
    SUM(CASE WHEN purchase_date BETWEEN '2004-09-01' and   '2004-09-30'  THEN amount ELSE 0 END) As m2004_09,
    SUM(CASE WHEN purchase_date BETWEEN '2004-10-01' and   '2004-10-31' THEN amount ELSE 0 END) As m2004_10, 
SUM(amount) As Total
FROM purchases WHERE purchase_date BETWEEN '2004-08-01' AND '2004-10-31'

^{Note the above assumes that your database handle dates using the ansi date format and uses ' for date delimiter. This varies from database type to database type as well as configuration settings in the database server. For example Microsoft Access uses # to delimit dates, while most databases use '}

DBMS specific approaches

Microsoft Access

Microsoft Access has supported cross-tab queries natively for as far back as I can remember which would be Access 2.0. Access 1.0 may have supported it as well, but my memory doesn't go that far back.

Access supported via a predicate called TRANSFORM. If you are using the Access wizard, you can use the cross-tab wizard to automatically write the SQL statement for you.

The above query would look like

TRANSFORM Sum(purchases.amount) AS SumOfamount
SELECT Sum(purchases.amount) AS Total
FROM purchases
WHERE purchase_date between #8/1/2004# and #10/31/2004#
PIVOT "m" & Format([purchase_date],"yyyy_mm") In ("m2004_08","m2004_09","m2004_10")

Note: The In clause above is optional unless you plan to use this as a source for a report. What the In clause assures is that you always get the same consistent number of columns even if no data was present for say August. If you left it out and you had no data present for a specific month, then no column would be generated for that month.

Microsoft SQL Server 2005

In SQL Server 2005, the PIVOT and UNPIVOT predicates were introduced. The PIVOT predicate is a new way of doing crosstab queries that is very similar to MS Access Transform PIVOT approach. The code looks like the below

SELECT *
FROM 
(SELECT  'm' + CAST(DATEPART(year,[purchase_date]) As varchar(4)) + '_' + RIGHT('0' + CAST(DATEPART(month,[purchase_date]) As varchar(2)), 2) as themonth, amount 
FROM purchases 
WHERE purchase_date between '8/1/2004' and '10/31/2004' ) p 
PIVOT (SUM(p.amount) FOR themonth  In ([m2004_08],[m2004_09],[m2004_10])) as pvt
  

Articles of Interest
CrossTab Queries in PostgreSQL using tablefunc contrib	Covers using crosstab function packaged in the postgres tablefunc contrib module in order to generate cross tab queries
Table-Driven and Data Driven Programming	Example of using Table-Driven technique to generate SQL for a cross tab query
Dynamic Crosstab Queries	Example of how to create static and dynamic cross tabs in SQL Server 7. Provides stored procedure code for generating a cross tab from any query of data
Creating CrossTab queries in MS Access	Covers basics of creating crosstabs in MS Access and when to use them.