First, here’s the code for the procedure:
create procedure CrossTab (@Select varchar(1000),
@OtherCols varchar(100) = Null)
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 )
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.
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:
‘SELECT LastName, OrderDate FROM northwind..Employees Employees INNER JOIN northwind..Orders Orders ON (Employees.EmployeeID=Orders.EmployeeID) ‘,
‘select titles.*, stores.stor_name, sales.qty, sales.stor_id from pubs..titles titles
inner join pubs..sales sales on
inner join pubs..stores stores on sales.stor_id = stores.stor_id ‘,
‘SUM(qty ELSE 0)[Qty], MAX(stor_name ELSE ””)[MaxStoreName], COUNT(1 ELSE 0)[Count]’,
‘Count(*) as TotalCount’