Generating a Crosstab Report

Congratulations to Tony Bain, senior DBA at Wellington, New Zealand-based SQL Services and Zivan Karaman, manager of the statistical department at Limagrain Genetics in Chappes, France. Tony won first prize of $100 for the best solution to the November Reader Challenge, "Generating a Crosstab Report." Zivan won second prize of $50. Here’s a recap of the problem and the solution to the November Reader Challenge.

Problem

Ed is the application programmer for a company that specializes in selling products on the Web. The company’s application database is distributed across several SQL Server 2000, 7.0, and 6.5 systems. Ed needs to use data from the database’s Items table to write crosstab reports. The Items table tracks the products (up to 25) that a user adds to the shopping cart during a Web site visit, and the table’s OrderID column identifies each visit. Here is the schema for the Items table, with relevant columns:

CREATE TABLE Items (        OrderID int,        ItemNum tinyint NOT NULL CHECK ( ItemNum               BETWEEN 0 And 24 ),        Description varchar( 30 ),        PRIMARY KEY ( OrderID, ItemNum ))-- Sample Data:INSERT INTO Items VALUES( 1, 0, 'Item #1' )INSERT INTO Items VALUES( 1, 1, 'Item #2' )INSERT INTO Items VALUES( 1, 2, 'Item #3' )INSERT INTO Items VALUES( 1, 3, 'Item #4' )INSERT INTO Items VALUES( 1, 4, 'Item #5' )INSERT INTO Items VALUES( 1, 5, 'Item #6' )INSERT INTO Items VALUES( 1, 6, 'Item #7' )INSERT INTO Items VALUES( 2, 0, 'Item #1' )INSERT INTO Items VALUES( 2, 1, 'Item #2' )INSERT INTO Items VALUES( 2, 2, 'Item #3' )

Ed needs to generate a crosstab report that organizes order items in rows, showing a maximum of five items per row, per order. He can display only the OrderID and the item description in the report. Help Ed devise a method of generating this report that works efficiently on SQL Server 2000, 7.0, and 6.5.

Solution

Ed can approach the problem in two steps. First, he needs to split the item numbers into groups of five. To accomplish this task, he can use a simple mathematical trick that incorporates division and modulus operators. Because he stores the ItemNum value for each order starting with 0 and running through 24, he can use this value to group the items in fives. The following SELECT statement categorizes the item numbers:

SELECT i1.OrderID, i1.Description,       i1.ItemNum / 5 AS RowNum,       i1.ItemNum % 5 AS ColNum   FROM Items AS i1 

The query’s RowNum expression divides the ItemNum value by 5 to get the quotient. ColNum divides the ItemNum by 5 to retrieve the remainder. When you review this query’s output, you can see that each order’s ItemNum value can be classified into rows and columns. The query produces the following sample output for OrderID = 1:

OrderID  Description  RowNum  ColNum-----------  ---------------  ------------  -----------           1  Item #1                    0              0           1  Item #2                    0              1           1  Item #3                    0              2           1  Item #4                    0              3           1  Item #5                    0              4           1  Item #6                    1              0           1  Item #7                    1              1

Second, to generate the crosstab report, Ed can use a GROUP BY clause to group the data collected in the first step by the OrderID and the RowNum columns. Then, he can use the CASE expression and the MIN() aggregate function to display the items as columns. The following query generates the item groups for each order:

SELECT i1.OrderID,       i1.ItemNum / 5 AS RowNum,       MIN( CASE i1.ItemNum % 5 WHEN 0                THEN i1.Description END ) AS Item1,       MIN( CASE i1.ItemNum % 5 WHEN 1                THEN i1.Description END ) AS Item2,       MIN( CASE i1.ItemNum % 5 WHEN 2                THEN i1.Description END ) AS Item3,       MIN( CASE i1.ItemNum % 5 WHEN 3                THEN i1.Description END ) AS Item4,       MIN( CASE i1.ItemNum % 5 WHEN 4                 THEN i1.Description END ) AS Item5   FROM Items AS i1GROUP BY i1.OrderID, i1.ItemNum / 5ORDER BY i1.OrderID, i1.ItemNum / 5

This solution demonstrates how you can use simple schema design coupled with arithmetic operations to generate the required crosstab report for each order’s items. Moreover, the solution works efficiently on three SQL Server releases—2000, 7.0, and 6.5.