Paladin Consultants, LLC

"Good Information is just good business"

JA slide show
Home Resources Dynamic Crosstabs in SQL Server
Print E-mail

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:

May 23, 2006

Month

Y1992

Y1993

Y1994

2

0

1

0

3

0

1

0

5

0

6

0

6

3

0

9

0

0

8

10

0

1

0

12

0

1

0

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:

Month

Y1992

Y1993

Y1994

2

0

35

0

3

0

25

0

5

0

165

0

6

80

0

0

9

0

0

163

10

0

15

0

12

0

10

0

 

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.

/* Set up the stored procedure in Master, so it is accessible from all of the databases */

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

 

CREATE PROC up_XTAB
@table AS sysname, -- Table or view to Crosstab
@rownames AS nvarchar(128), -- Summary Key for rows
@rownamesalias AS sysname = NULL, -- Alias for grouping column
@columnnames AS nvarchar(128), -- Summary Key for Columns
@summarycell AS sysname = NULL -- Data cells
AS

 

/* Declare another variable to hold the actual sql command as it is created dynamically */

 

DECLARE
@sqlstring AS varchar(8000),
@NEWLINE AS char(1)
  SET @NEWLINE = CHAR(10)

 

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

 

SET @sqlstring =
'SELECT' + @NEWLINE +
' ' + @rownames + CASE

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 */

CREATE TABLE #keytable(keyvalue nvarchar(100) NOT NULL PRIMARY KEY) DECLARE @keysqlstring AS varchar(1000)
SET @keysqlstring =
'INSERT INTO #keytable ' +
'SELECT DISTINCT CAST(' + @columnnames + ' AS nvarchar(100)) ' +
'FROM ' + @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

SET @sqlstring = @sqlstring + @NEWLINE +
'FROM ' + @table + @NEWLINE +
'GROUP BY ' + @rownames + @NEWLINE +
'ORDER BY ' + @rownames

 

-- PRINT @sqlstring + @NEWLINE -- For debug
EXEC (@sqlstring)
GO

/* 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

We specialize is custom database software. Call us for a free consultation (973) 635 0080

 

 

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

 

 
Bookmark and Share