Crosstab queries for SQL Server 2000 with Access

from the Artful SQL Server & Access Tips List

SQL Server is stable, powerful, versatile and fast but it provides no way to build crosstab queries. Microsoft Access to the rescue.

In general, a crosstab query 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.

Listing A:

SELECT TOP 100 PERCENT
    dbo.Customers.CompanyName,
    YEAR(dbo.Orders.OrderDate) AS Year,
    SUM(dbo.[Order Subtotals].Subtotal) AS [Total Sales]
FROM
    dbo.Customers INNER JOIN
    dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID INNER JOIN
    dbo.[Order Subtotals] ON dbo.Orders.OrderID = dbo.[Order Subtotals].OrderID
GROUP BY 
    dbo.Customers.CompanyName, 
    YEAR(dbo.Orders.OrderDate)
ORDER BY 
    dbo.Customers.CompanyName, 
    YEAR(dbo.Orders.OrderDate)

Listing B:

SELECT TOP 100 PERCENT
    dbo.Customers.CompanyName,
    SUM(dbo.[Order Subtotals].Subtotal) AS [Total Sales],
    SUM(CASE Year(OrderDate) WHEN 1997 THEN dbo.[Order Subtotals].Subtotal ELSE 0 END) AS [1997],
    SUM(CASE Year(OrderDate) WHEN 1998 THEN dbo.[Order Subtotals].Subtotal ELSE 0 END) AS [1998]
FROM
    dbo.Customers INNER JOIN dbo.Orders
    ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
    INNER JOIN dbo.[Order Subtotals]
    ON dbo.Orders.OrderID = dbo.[Order Subtotals].OrderID
GROUP BY
    dbo.Customers.CompanyName
ORDER BY
    dbo.Customers.CompanyName

I often use Microsoft Access, and specifically Access Data Project (ADP) files, so I can take advantage of its graphical query builder. However, Access excludes the crosstab wizard when using an ADP file. There is a solution: 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 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.
Return to the Artful SQL Server & Access tips page