Another Dynamic SQL CrossTab Stored Procedure
First off, before going any further make sure you have read the hall of fame SQLTeam article by Rob Volk on generating crosstab results using a flexible, dynamic stored procedure that has been viewed over 100,000 times! This entire concept and pretty much all of the ideas I've had regarding this topic and this techinique in general are all due to Rob's great work and his very clever stored procedure.
It must be crosstab season or something, because lately I've been getting quite a few emails and comments about an alternative stored procedure that I've posted in the comments to that article that has been helping quite a few users. To potentially help others out there with this common request (which I still feel should be mostly done at the presentation layer, but I suppose it's not always possible) here's a quick recap/reprint of my adaptation of Rob's excellent idea and some notes.
The main difference between Rob's original stored procedure and mine are that
- you can summarize multiple values
- it only creates columns based on your actual select statement ( not based on all rows in a table)
- it works fine for multi-users (no global temp tables)
- it allows you to easily specify default values for your pivots
Note that if you read the comments from the article, you'll see lots of modifications and adaptations of Rob's original that do address some of these issues, but I am proud of the brevity and flexibility of my code and I feel that it works well and is easily modified (see the end of this article for one idea). Depending on your needs, it may be useful to incorporate some of the other ideas presented in the article and the comments, so be sure to do some research if necessary or if you are interested in learning more.
First, here's the code for the procedure:
create procedure CrossTab (@Select varchar(1000),
@PivotCol varchar(100),
@Summaries varchar(100),
@GroupBy varchar(100),
@OtherCols varchar(100) = Null)
AS
set nocount on
set ansi_warnings off
declare @Vals varchar(8000);
set @Vals = '';
set @OtherCols= isNull(', ' + @OtherCols,'')
create table #temp (Pivot varchar(100))
insert into #temp
exec ('select distinct convert(varchar(100),' + @PivotCol + ') as Pivot FROM (' + @Select + ') A')
select @Vals = @Vals + ', ' +
replace(replace(@Summaries,'(','(CASE WHEN ' + @PivotCol + '=''' +
Pivot + ''' THEN '),')[', ' END) as [' + Pivot )
from #Temp
order by Pivot
drop table #Temp
exec ( 'select ' + @GroupBy + @OtherCols + @Vals +
' from (' + @Select + ') A GROUP BY ' + @GroupBy)
set nocount off
set ansi_warnings on
And here's a description of the parameters and how to use it:
- Select - This is the SQL statement or table you are querying. It can be any valid SELECT statement, with JOINS, WHERE clauses, TOP, etc -- anything that you can cut and paste into query analyzer and execute. (And, in fact, you should always do this when testing things out).
- PivotCol - This is the column (or a valid expression) that you wish to pivot on.
- Summaries - The summaries you wish to perform. Note that this is plural; you can summarize multiple columns if you wish. See below for more information.
- GroupBy - This is the list of the non-pivot columns you wish to group by and return, separated by commas.
-
OtherCols (optional) - Any other columns to return that you are not grouping by, also separated by commas. Make sure that this list does not contain references to columns that are not being summarized or grouped.
Here's the format you should use when specifying the Summaries parameter:
SUM(Field ELSE DefaultValue)[Prefix], ....
First, list the aggregate function you wish to use, and within that function put the column you wish to summarize, and add an ELSE clause with what that column's default value is. For example, put 0 if you wish to display all 0's for empty columns, or NULL if you wish to display nulls.
Next, immediately following the aggregate function (with no spaces) put the prefix for the column name in brackets. This is what will be appended to the beginning of the column name, followed by the value from the pivot column. Leave as [] to just use the pivot column's value as the field name.
Finally, you may have multiple summaries just seperate them by comma's. Remember if you have many pivot values, you will probably end up with too many columns and/or a T-SQL statement > 8000 characters so you may get an error.
Examples:
If the pivot column is "Employee", with values of 001, 002, 003 and 004:
SUM(Hours ELSE 0)[]
returns column headings of 001, 002, 003, 004, and returns a 0 in all "empty" fields.
SUM(Hours ELSE 0)[Hours], MAX(Date ELSE Null)[MaxDate]
returns column headings of Hours001, MaxDate001, Hours002, MaxDate002, Hours003, MaxDate003, ..etc ...
SUM(Amount ELSE Null)[]
returns column headings of 001,002,003,004 and returns a NULL in all "empty" fields. Note that you can use SUM(Amount)[] as a shortcut, since ELSE NULL is redundant and is the default behavior.
SUM(Amount ELSE 0)[Amount], COUNT(Amount)[Qty]
returns column headings of Amount001, Qty001, Amount002, Qty002, ... etc ...
Here are some samples using either Northwind or Pubs:
exec CrossTab
'SELECT LastName, OrderDate FROM northwind..Employees Employees INNER JOIN northwind..Orders Orders ON (Employees.EmployeeID=Orders.EmployeeID) ',
'Year(OrderDate)',
'Count(LastName)[]',
'LastName'
exec CrossTab
'select titles.*, stores.stor_name, sales.qty, sales.stor_id from pubs..titles titles
inner join pubs..sales sales on
(sales.title_id=titles.title_id)
inner join pubs..stores stores on sales.stor_id = stores.stor_id ',
'stor_id',
'SUM(qty ELSE 0)[Qty], MAX(stor_name ELSE '''')[MaxStoreName], COUNT(1 ELSE 0)[Count]',
'title',
'Count(*) as TotalCount'
Modification Ideas
A common problem with these procedures is when there are too many columns and the SQL statement stored in the variable ends up with a length of more than 8000 characters, causing truncation problems and syntax errors.
Click here for a modification of the stored procedure which allows you to extend the length of the SQL that can be generated fairly easily that seems to work well:
