This tip continues the system stored procedure series with a routine to perform crosstab operations. One of the most popular features of Microsoft Access is the crosstab functionality. When Access database applications move to SQL Server the Access query engine is left behind and the crosstab feature is often sorely missed. One of the most common questions in the SQL Server forums is how to perform a similar crosstab operation using T-SQL. I think you will find this stored procedure to be a great answer. It goes well beyond any other free tool I have seen. It was designed to provide crosstab functionality very much like 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. A crosstab is very useful for a variety of data analysis purposes. 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. The parameter controls the number of columns and the positions 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. Please be aware that web page formatting may have made a parameter value wrap to a second line in the examples below. If so, remove the extra CR/LF before executing the code. The example below 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' The example below 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' The example below 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, 1 The example below extends the example immediately above 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.