|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.50Now 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 , SUM(CASE Year(OrderDate) WHEN 1998 THEN dbo.[Order Subtotals].Subtotal ELSE 0 END) AS  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.CompanyNameI 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.40The 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.
Last updated 22 May 2009