|Back to Papers and Articles|
What is a cross tab query and how do you create one using a relational database?
Copyright 2006 Paragon Corporation ( December 26, 2006)
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
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.
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
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 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
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
Back to Papers and Articles