|
This tip continues the system stored procedure series with a routine to perform crosstab operations.
One of Microsoft Access's most popular features is the crosstab functionality, which is useful for a variety of data analysis purposes. When Access applications are moved to SQL Server, the Access query engine is left behind and the crosstab feature is often sorely missed. DBAs then turn to SQL Server forums asking questions on how to perform crosstab operations using T-SQL. I think you will find this stored procedure offers a great answer. It goes well beyond any other free tool that I have seen, and it was designed to provide crosstab functionality similar to that of Access.
A crosstab calculates an aggregation on a certain column (A) in a set of rows. It then transforms the unique values in another column (X) into column names for the result. All other columns (Y) are used for grouping and their unique values define the rows for the result. The aggregated values of A are distributed among columns in the result according to the value of X and among rows in the result according to the value of Y.
The SQL code in Listing 1 creates a system stored procedure named sp_Crosstab. The routine takes a specified set of data and summarizes it in a specified way. The summarization is usually called a crosstab, but it's also known as pivoting or rotating. SQL Server provides no native tools to do a crosstab, so the task usually involves either writing custom T-SQL code (often lengthy and complicated) or bringing the data into an external application. This flexible stored procedure makes crosstabs a simple operation.
The sp_Crosstab stored procedure accepts up to 11 parameters, but only four of them are required.
The first parameter (@DBFetch) specifies the set of data to summarize. The parameter can be a table name (including a temporary table), view name, user-defined function call or SELECT statement. The rows of @DBFetch must include the two columns specified by the @DBField and @PCField parameters, and at least one other column to be used for grouping.
The second parameter (@DBWhere) is optional and it specifies a WHERE clause to filter the data specified by the @DBFetch parameter.
The third parameter (@DBPivot) is optional and it specifies the column names to appear in the result set. It also controls the number and sequence of columns. The parameter can be a list of column names separated by pipes (vertical bars) or it can be a SELECT statement. If column names are supplied with @DBPivot they must match values in the column specified by the @DBField parameter. If column names are not supplied with @DBPivot the column names are generated from values in the column specified by the @DBField parameter.
The fourth parameter (@DBField) specifies the column containing the data that becomes result set column names. The values in this column determine the result set column into which the aggregated column values are summarized.
The fifth parameter (@PCField) specifies the column to be aggregated.
The sixth parameter (@PCBuild) specifies the aggregation to be performed (COUNT, SUM, MIN, MAX or AVG).
The seventh parameter (@PCAdmin) is optional and it specifies a result set column name to represent null values.
The eighth parameter (@DBAdmin) is optional and it specifies whether any additional columns should be added to the result set for totals. A value of zero (0) means no additional columns. A value of one (1) means one additional total column using the aggregation function specified by the @PCBuild parameter. A value of two (2) means three additional total columns using the aggregation functions MIN, MAX and COUNT. A value of three (3) means four additional total columns using the aggregation functions as mentioned under 1 and 2.
The ninth parameter (@DBTable) is optional and it specifies a table name for the result set. If the table already exists it will be dropped (subject to the DBUltra parameter). The default action is to return the result set with a SELECT statement. This parameter allows the result set to be saved in a table for further manipulation. The table can be a permanent table or a global temporary table.
The tenth parameter (@DBWrite) is optional and it specifies a database name for the @DBTable parameter. The default is the current database.
The eleventh parameter (@DBUltra) is optional and it specifies whether the result set table (if specified) should be dropped (if it already exists) before saving the result set. A value of zero (0) means the table is dropped and created again. A value of one (1) means the result set is to be appended to an already existing table.
It's awkward to embed single quotes in a string literal with T-SQL. For convenience when specifying string parameters, caret symbols (^) are converted to single quotes in @DBFetch and @DBWhere. This feature is demonstrated in an example below. The sp_Crosstab stored procedure creates (and later deletes) a global temporary table to determine the columns in the set of data to be summarized. The global temporary table name includes the @@SPID function value to ensure that the name is unique for each connection.
The sp_Crosstab stored procedure takes several measures to make a SQL injection attack more difficult. The methods are discussed in a previous tip.
This routine is intended mainly for ad hoc data analysis purposes. It employs methods -- such as using dynamic SQL -- that may not be optimal for use within a production application. Please read my earlier tips for the usual cautions. If you intend to execute this stored procedure from within a production application, be sure to test it thoroughly in that environment before deployment.
Example 1
This example summarizes product sales by returning order quantities for each product at each level of discount. The data comes from the Order Details Extended view in the Northwind database.
USE Northwind EXECUTE sp_Crosstab
'SELECT ProductName, Quantity, Discount FROM [Order Details Extended]',
NULL,
NULL,
'Discount',
'Quantity',
'SUM'
Example 2
This example summarizes part of the database design by returning column counts for each table and each column data type. The data comes from the Northwind database through a standard system view.
USE Northwind EXECUTE sp_Crosstab
'SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.Columns',
NULL,
NULL,
'DATA_TYPE',
'COLUMN_NAME',
'COUNT'
Example 3
This example extends the example immediately above by limiting which values in the DATA_TYPE column should be summarized and adding a total column.
USE Northwind EXECUTE sp_Crosstab
'SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.Columns',
NULL,
'char|varchar|nchar|nvarchar',
'DATA_TYPE',
'COLUMN_NAME',
'COUNT',
NULL,
Example 4
This example extends example 3 by adding a WHERE clause to filter the set of data to be summarized. The WHERE clause makes the total column reflect only the aggregation values included in the result set, and it also removes tables without any character columns from the result set.
USE Northwind EXECUTE sp_Crosstab
'SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.Columns',
'DATA_TYPE LIKE ^%char^',
'char|varchar|nchar|nvarchar',
'DATA_TYPE',
'COLUMN_NAME',
'COUNT',
NULL,
1
I hope you find this system stored procedure to be useful.
| 
