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:
