Sunday, August 9, 2009

A Simple Way to Create Crosstab Queries in SQL Server - Part 2

Previously, I demonstrated a simple technique for creating SQL queries that produce crosstab-style output. The technique involves wrapping CASE statements in aggregate functions, which makes for clean, readable source.

In the previous examples, I used hardcoded individual cases to compute each shipper in its own column. While easy to do, this isn't exactly a data-driven solution. It would be nice, for example, to be able to add a fourth shipper to our database without having to recode these crosstab queries.

Fortunately, thanks to dynamic SQL, with a little more work we can construct our CASE statements by querying the Shippers table directly. To do this we'll define a variable @cases to hold the portion of the SELECT clause that comprises our computed columns. The template for each CASE statement looks like the following, with fields from the Shippers table in {curly braces}:
, sum(CASE WHEN ShipVia = {ShipperID} THEN Freight ELSE 0 END) as [{CompanyName}]

Notice the leading comma in the template. Since we're grouping by CustomerID and listing it first in the SELECT clause, we can have a leading comma in our first and each subsequent case. The first part of our SQL script queries the Shippers table to construct each CASE statement, storing the concatenated strings in the @cases variable.
declare @cases varchar(8000)
set @cases = ''
select @cases
= @cases + ', sum(CASE WHEN ShipVia = '
+ Convert(varchar(5),ShipperID)
+ ' THEN Freight ELSE 0 END) as [' + CompanyName + ']'
from Shippers

The second part of the script wraps our cases in the full SQL statement to aggregate by CustomerID. Since this is a dynamic SQL statement, we'll wrap the whole thing in an execute command to produce the desired crosstabular output:
'SELECT CustomerID '
+ @cases
+ ' from Orders Group By CustomerID Order by CustomerID'

This technique becomes even more attractive when there are several crosstab columns to be defined. Consider the following example which computes total sales by customer, broken out by product categories in columns. Because there is some complexity to joining the necessary detail tables together, the first part of this script computes a subquery in the variable @sqlInner. The subquery is then aggregated, with cases computed dynamically by querying the Categories table. The final execute statement produces the crosstab output.

declare @sqlInner varchar(8000)
set @sqlInner = '
select o.CustomerID
, (od.UnitPrice * od.Quantity) - od.Discount as Sales
, p.CategoryID
from [order details] as od
inner join Products p on od.ProductID = p.ProductID
inner join Orders o on od.OrderID = o.OrderID
) x

declare @cases varchar(8000)
set @cases = ''
select @cases
= @cases
+ ', sum(CASE WHEN CategoryID = '
+ Convert(varchar(5),CategoryID)
+ ' THEN Sales ELSE 0 end) as [' + CategoryName + ']'
from Categories

'SELECT CustomerID as [Total Sales by Customer]'
+ @cases
+ ' from ' + @sqlInner
+ ' Group By CustomerID Order by CustomerID'

With the help of dynamic SQL execution, CASE statements that support columns in crosstabular output may be generatated in a data-driven way. By querying the appropriate table for individual cases, users may add or modify data without the need to recode the crosstab query.


Post a Comment

Submit a comment?