AlphaPress Publishing
P.O. Box 5942 Albany, NY 12205
Tel: 518-250-3890
Email: alphapress@hotmail.com  


  
Click to buy this book from Amazon

Section Menu 

News

Pindaro is now working on a comprehensive book on SQL Server 2014.  For any comments you can email the author at alphapress@hotmail.com.

  

 

 

The structure of a crosstab query in detail

To contact the author click here

This article is from the book "Access 2007 Pure SQL

To download the sample database click here


A crosstab query in detail

Find total sales by state and year

Discussion:

As you can see from the design view and the SQL code, we use three fields to create this very informative crosstab query.  The first is the state field, which we use as the row heading.  The second is the OrderDate field, which we use for column headings.  For the OrderDate field there is a catch:  We use the year() function to extract only the year out of the multitude of order dates we have in the database.  The third field is the value field, which we call OrderTotal.  This is a calculated field that sums the unitprice * the quantity of each product for each of our customer orders. 

When you look at the SQL code, you will notice the use of a new statement called TRANSFORM.  The TRANSFORM statement relates to the value field in a crosstab query, and it always precedes the SQL statement.  Additionally, it is always followed by the aggregate function used for the value field, which in this case is sum(Quantity * UnitPrice).  Below is the SQL code for the crosstab query explaining it step by step: 

TRANSFORM Sum([unitprice]*[quantity]) AS Ordertotal

The TRANSFORM statement is used for the value field in a crosstab query. 

SELECT State FROM Qry_Crosstab_Base

The SELECT statement is used only for the row-heading field of the crosstab query, which in this case is the State field. 

GROUP BY State

The GROUP BY clause follows next, and it is applied on the same field used in the SELECT statement.  Since we used State for the SELECT statement, we use State for the GROUP BY clause as well. 

PIVOT Year([OrderDate])

The PIVOT statement is the last one used in a crosstab query, and it is applied on the column-heading field. 

Below is the general structure of a crosstab query in SQL code. 

TRANSFORM ValueField

SELECT ColumnField

FROM DataSource

GROUP BY ColumnField

PIVOT RowField
 

Design Code:


Code:

TRANSFORM sum([unitprice]*[quantity]) AS Ordertotal

SELECT State

FROM Qry_Crosstab_Base

GROUP BY State

PIVOT Year([OrderDate]) 

Looking at the results below, we can quickly understand and compare sales volumes by each state and each year.  For example, the biggest sales revenues come from California, and they grow year by year.  In Florida, we need to have a look at what is happening because sales dropped a lot for 2010.  This is the power of crosstab queries. We get summarized results in seconds.