MS SQL Server Tutorial: 100% Working Dynamic CrossTab in MSSQL Server 2000

create procedure up_CrossTab (@SelectStatement varchar(1000),
@PivotColumn varchar(100),
@Summary varchar(100),
@GroupbyField varchar(100),
@OtherColumns varchar(100) = Null)
AS
/*
Inputs are any 1000 character or less valid SELECT sql statement,
the name of the column to pivot (transform to rows), the instructions to summarize the data, the field you want to group on, and other fields returned as output. 1
*/
set nocount on
set ansi_warnings off

declare @Values varchar(8000);
set @Values = '';

set @OtherColumns= isNull(', ' + @OtherColumns,'')
/*
An 8000 varchar variable called @values is created to hold the [potentially filtered] values in the pivot column. @Values is initiated to an empty string. Then, a temporary table is created to hold each unique value. After the table is created, its rows are loaded into the variable @values. It's usefullness completed, the temporary table is destroyed. 2
*/
create table #TempTbl (Tempfield varchar(100))

insert into #TempTbl
exec ('select distinct convert(varchar(100),' + @PivotColumn + ') as Tempfield FROM (' + @SelectStatement + ') A')

select @Values = @Values + ', ' +
replace(replace(@Summary,'(','(CASE WHEN ' + @PivotColumn + '=''' +
Tempfield + ''' THEN '),')[', ' END) as [' + Tempfield )
from #TempTbl
order by Tempfield

drop table #TempTbl
/*
Finally, a dynamic sql select statement is executed which takes the GroupByField, and OtherColumns, passed into the procedure, and each of the Values from the Pivot Column from the passed in SELECT statement . 3
*/
exec ( 'select ' + @GroupbyField + @OtherColumns + @Values +
' from (' + @SelectStatement + ') A GROUP BY ' + @GroupbyField)

set nocount off
set ansi_warnings on
GO

/*
1 Here's an example of calling the procedure, showing the SELECT statement, the Pivot Column, the Summary instruction, and the GroupBy Column:

exec up_CrossTab
'SELECT LastName, OrderDate, Freight FROM northwind..Employees Employees INNER JOIN northwind..Orders Orders ON (Employees.EmployeeID=Orders.EmployeeID) ',
'Year(OrderDate)',
'sum(freight)[]',
'LastName'

Note that the OtherColumn parameter was omitted. However, FirstName and Title might be obvious candidates. They must appear in the last parameter, separated by a comma, and also in the SELECT statement parameter.

2 In our example the different values in the PivotColumn parameter are gathered into the @values variable. Note that we needed to modify that value in our example using the YEAR function. Otherwise, each order date would have it's own column, and might, given enough data, overwhelm the 8000 character limitation of the variable itself.

3 Limiting or qualifying data may be donw either by using a Where clause in the SELECT parameter, or a case or an else clause in the summary parameter, such as SUM( Freight ELSE Null)[].
*/