Paladin Consultants, LLC

Microsoft Access provides some convenient ways to create Crosstabs using the PIVOT and TRANSFORM keywords. In fact, one can use the graphic query wizard to create many queries in Access and upsize then to SQL Server. However, SQL Server does not support some of the Access SQL language as a result this technique is not available for Crosstabs and many other procedures.

One quick fix solution which others have used (although I believe it to be a distinctly inferior solution) is using an interim result set back to the desktop for further processing. I think this is inefficient and ultimately defeats the purpose of the client-server data model.

Static Crosstab queries are an easy fix for this situation when one knows beforehand exactly what values the result set will contain. I have written several articles on those techniques which are reproduced elsewhere on this website.

Consider a Crosstab query where one knows the expected output will be in years 1992-1994. One could make the following SQL call, using the CASE keyword to segregate the different years into a summary count query:

Count of Orders by Month and Year

USE pubs
SELECT
MONTH(Ord_Date) AS OrderMonth,
SUM(CASE YEAR(Ord_Date)
WHEN 1992 THEN 1
ELSE 0
END) AS Y1992,
SUM(CASE YEAR(Ord_Date)
WHEN 1993 THEN 1
ELSE 0
END) AS Y1993,
SUM(CASE YEAR(Ord_Date)
WHEN 1994 THEN 1
ELSE 0
END) AS Y1994
FROM Sales
GROUP BY MONTH(Ord_Date)
ORDER BY MONTH(Ord_Date)

Such a query would yield this result:

And, if you wanted to so a sum of the quantities per month, this SQL would work:

Sum of Orders by Month and Year

USE pubs
SELECT
MONTH(Ord_Date) AS OrderMonth,
SUM(CASE YEAR(Ord_Date)
WHEN 1992 THEN qty
ELSE 0
END) AS Y1992,
SUM(CASE YEAR(Ord_Date)
WHEN 1993 THEN qty
ELSE 0
END) AS Y1993,
SUM(CASE YEAR(Ord_Date)
WHEN 1994 THEN qty
ELSE 0
END) AS Y1994
FROM Sales
GROUP BY MONTH(Ord_Date)
ORDER BY MONTH(Ord_Date)

Which would yield this result:

For the many occasions when you don't know in advance what the results will be, only a dynamic query will do. This article describes a technique and creates a stored procedure which will produce such a dynamic Crosstab which will work regardless of the table and fieldnames.

In a nutshell, you need to make a stored procedure that accepts parameters for the table (or view) name, and the salient rows and columns, aliases and summary technique. Next, a SQL statement is created to extract the unknown values for the row headings into a temporary table. Finally, loop through the temporary table creating another SQL statement which will produce the final result set.

USE master
/* Set up 5 variables to accommodate the information to create any Crosstab */

/* Select the rownames and their alias (if any) when the row is not null) */

WHEN @rownamesalias IS NOT NULL THEN ' AS ' + @rownamesalias
ELSE ''
END

/* Stored all the different key values to be used as column names in the Crosstab into a temporary table */

EXEC (@keysqlstring)

DECLARE @key AS nvarchar(100)
SELECT @key = MIN(keyvalue) FROM #keytable

/* Complete the master sql string by looping through the temporary key table */
WHILE @key IS NOT NULL BEGIN
SET @sqlstring = @sqlstring + ',' + @NEWLINE +

/* Some manipulation of the following CASE statement can make this technique work for filling the summary cells with MAX, MIN values, Average, etc */
' SUM(CASE CAST(' + @columnnames +
' AS nvarchar(100))' + @NEWLINE +
' WHEN N''' + @key +
''' THEN ' + CASE
WHEN @summarycell IS NULL THEN '1'
ELSE @summarycell
END + @NEWLINE +
' ELSE 0' + @NEWLINE +
' END) AS c' + @key
SELECT @key = MIN(keyvalue) FROM #keytable
WHERE keyvalue > @key
END

/* Some examples of the dynamic query in action using the sample SQL Server databases */

USE Northwind
EXEC up_XTAB
@table = 'Orders',
@rownames = 'MONTH(OrderDate)',
@rownamesalias = 'OrderMonth',
@columnnames = 'YEAR(OrderDate)'

USE Pubs
EXEC up_XTAB
@table = 'Sales',
@rownames = 'stor_id',
@columnnames = 'YEAR(ord_date)',
@summarycell = 'qty'

Another article on this subject appears in Improved Dynamic Crosstabs which will provide additional perspective and depth to this subject.

Metro NY / NJ SQL Server Consultants

Computer Consultants: Database Development
Computer Programming: Web Site Services
Computer Programming: Custom Software
Computer Consulting: IT Consulting
Paladin Consultants, LLC Home Page
Computer Consulting: Contact Us