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
Such a query would yield this result:May 23, 2006
And, if you wanted to so a sum of the quantities per month, this SQL would work:
Sum of Orders by Month and Year
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./* Set up the stored procedure in Master, so it is accessible from all of the databases */
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
/* Declare another variable to hold the actual sql command as it is created dynamically */
@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
/* 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
SET @sqlstring = @sqlstring + @NEWLINE +
'FROM ' + @table + @NEWLINE +
'GROUP BY ' + @rownames + @NEWLINE +
'ORDER BY ' + @rownames
-- PRINT @sqlstring + @NEWLINE -- For debug
/* Some examples of the dynamic query in action using the sample SQL Server databases */
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 ConsultantsWe specialize is custom database software. Call us for a free consultation (973) 635 0080
Computer Consultants: Database Development