Cross-tab queries are an extremely common requirement in
database applications. Given SQL Server’s stability, performance, and range of
tools, it is surprising that no facility is provided for building cross-tab
queries. But before I demonstrate how to build cross-tab queries, I’ll review
what a cross-tab report does.

In general, it takes rows from a table, often summing them,
and then presents the group totals as columns rather than rows. For instance,
you might need to create a report that summarizes sales by customer by year,
presenting the sum of sales for each year as a column. The leftmost column
would display the customer name, and successive columns would show the total
sales for that customer during each year in the range.

Listing A shows you how to create the view using the Northwind database. This will give you a result set that looks like this, in
which only the first customer is shown:

CompanyName           Year    Total Sales
AlfredsFutterkiste   1997     $2,022.50
AlfredsFutterkiste   1998     $2,250.50

Now you can see the problem. You need two columns, headed by
their years and containing the total sales amount. One way to do it is to
create columns that sum the Subtotals conditionally, as in Listing B.

This works well, but it’s quite tedious and isn’t very
scalable. We need to know the range of years in advance and create columns
named after those years. Furthermore, the whole operation is manual. If we
expand the range of years, then we’re back to editing the view. Granted, we can
simply copy and paste the last column and perform a quick edit, but that’s a
long way from automation.

I often use Microsoft Access and specifically Access Data
Project (ADP) files so that I can take advantage of its graphical query
builder. However, Access excludes the crosstab wizard
when using an ADP file. But we can create a standard MDB file instead, and then
link it to the tables in the Northwind database. This
requires creating a Data Source Name file, but the wizard lets us do this
easily. Be sure to test the connection to ensure that it works, and then select
the Customers, Orders Order Subtotals tables. (In a real situation, you may
need more tables and/or views.) The next step is to create a query that joins the
three tables and gives us the columns of interest.

In the Access database window, select the Queries tab and
then click the New button on the toolbar. (Don’t use
the Create Query By Using wizard within the
window—this gives you a list of wizards, and we only want the Crosstab Query wizard.) Select the query you just created
as the data source. From there, simply follow the wizard’s prompts.

The result will look similar to this:

CompanyName         Total Of Sub…       1996       1997       1998
AlfredsFutterkiste    $4,273.00   $2,022.50  $2,250.50

Ana Trujillo Emp…      $1,402.95      $88.80    $799.75    $514.40

The significant advantage of creating the crosstab query this way is that there is no more need to
specify the columns or even to define the range of years. The crosstab query takes care of it by using Access’s unique
flavor of SQL:

TRANSFORM Sum(SalesByCustomerByYear_qry.Subtotal) AS SumOfSubtotal

SELECT SalesByCustomerByYear_qry.CompanyName,
 Sum(SalesByCustomerByYear_qry.Subtotal) AS [Total Of Subtotal]

FROM SalesByCustomerByYear_qry

GROUP BY SalesByCustomerByYear_qry.CompanyName

PIVOT SalesByCustomerByYear_qry.Year;

I’m a big fan of choosing the right tool for the job. Given
SQL Server’s lack of a cross-tab wizard, I’m fully prepared to lean on Access
to provide it. At the end of the day, management is usually more interested in
results being valid than how you obtained those results.

TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!