Application Development Group

Cross-Tab Query for SQL Server

A common issue I run into is the need to display data in a chart that is to perform a Cross-Tab query.  Lets take for example the North Wind sample database in Microsoft DB products and assume that we need to create a report that will list the number qty of products sold for each product category during each month of the year.  The data to gather this information is stored in several tables.

So to get a list of Qty Sold for each category for each month and year the following query will gather this for us.

  SELECT CategoryName, month(OrderDate) as Month , 
         year(OrderDate) as Year, sum(quantity) as QTY
  FROM   orders inner join
         [order details] od on od.orderID = orders.orderid inner join
         products prod on prod.productID = od.ProductID inner join
         categories cat on cat.CategoryID = prod.CategoryID
  GROUP BY CategoryName, year(orderdate), month(orderdate)

This gives us a result similar to this:

This table gives us the raw data that we are looking for, but often is is easier to view the data in this manner

This version shows the same data, but is much easier to read.

So how do we convert from the first version to the second?

The method described here is called The Rozenshtein Method after the mathmetician who created it.  By using this query, we get almost exactly what we are after.

SELECT  CategoryName, year(orders.orderdate) as Year,
  SUM(Quantity*(1-ABS(SIGN(DatePart(mm,OrderDate)-1)))) AS Jan, 
  SUM(Quantity*(1-ABS(SIGN(DatePart(mm,OrderDate)-2)))) AS Feb, 
  SUM(Quantity*(1-ABS(SIGN(DatePart(mm,OrderDate)-3)))) AS Mar, 
  SUM(Quantity*(1-ABS(SIGN(DatePart(mm,OrderDate)-4)))) AS Apr, 
  SUM(Quantity*(1-ABS(SIGN(DatePart(mm,OrderDate)-5)))) AS May, 
  SUM(Quantity*(1-ABS(SIGN(DatePart(mm,OrderDate)-6)))) AS Jun, 
  SUM(Quantity*(1-ABS(SIGN(DatePart(mm,OrderDate)-7)))) AS Jul, 
  SUM(Quantity*(1-ABS(SIGN(DatePart(mm,OrderDate)-8)))) AS Aug, 
  SUM(Quantity*(1-ABS(SIGN(DatePart(mm,OrderDate)-9)))) AS Sep, 
  SUM(Quantity*(1-ABS(SIGN(DatePart(mm,OrderDate)-10)))) AS Oct, 
  SUM(Quantity*(1-ABS(SIGN(DatePart(mm,OrderDate)-11)))) AS Nov, 
  SUM(Quantity*(1-ABS(SIGN(DatePart(mm,OrderDate)-12)))) AS Dec
FROM Orders INNER JOIN
     [Order Details] od on od.OrderID = Orders.OrderID INNER JOIN
     Products on products.productID = od.ProductID INNER JOIN
     Categories on categories.CategoryID = products.CategoryID
GROUP BY categories.CategoryName, year(orders.orderdate)
ORDER BY year(orders.orderdate),Categories.CategoryName

This gives us results like this.

So how does this work?

The key is in the following line (and the ones like it).

SUM(Quantity*(1-ABS(SIGN(DatePart(mm,OrderDate)-7)))) AS Jul

What exactly is this line doing?

Lets start in the middle and work out from there.

(1-ABS(SIGN(DatePart(mm,OrderDate)-7)))

• DatePart(mm,OrderDate) gives us the month number of the order date, so an order in January would return a 1, February a 2 and so on.

   

• DatePart(mm,OrderDate)-7 simply subtracts seven from the month number notice that this is changed the the same as the month number for each line.

   

• Sign() is a sql function that returns 1 for positive numbers, 0 for zero and -1 for negative numbers.  So looking at it in this code, if the order date is in July then the value returned here is:
    DatePart(mm,OrderDate) will equal 7, then we will be looking at SIGN(7-7) or SIGN(0)which equals 0.
  
  Now lets say the order was in March.
    DatePart(mm,OrderDate) will equal 3.  This will give us SIGN(3-7) or SIGN(-4) which equals -1
  
  Lastly we’ll look at a October order.
    DatePart(mm,OrderDate) will equal 10.  This will give us SIGN(10-7) or SIGN(3) which equals 1

   

• The ABS function returns the absolue value of a number, in this case the value returned by the SIGN function. Absolute value in simple terms means that we drop the sign on a numeric value so ABS(15) = 15 and ABS(-15) = 15. ABS(0) will equal 0.  Since the SIGN fuction is returning 1, 0 or -1 this means that the returned value here will be 0 if the month of the order is the same as the line being calculated or 1 if any other month.

   

• Subtracting the value returned by the ABS function from 1 will give us the opposite value of the value returned by the ABS function. So if the order were in July, the ABS function will return a 0. 1–0=1 so the value at this point is 1.  If the order were in any other month the the ABS function will return a 1. 1-1=0 so the value would be 0.

So the above section of code simply returns a 1 if the order is from the month in question or a 0 if it is not.  Now we multiply the Qty by that value which will result in the Qty if the order is in the specified month or 0 if it is not. 

Sum() is a sql function to total the values of the given field(s) for all records.  So the Sum() function here will total the Quantity field for every record, however since the Quantity field is multiplied by 1 if it is for the correct month, and 0 if not then the total will only be the sum of the quantities for the month in question.