CREATE PROCEDURE crosstab 
			@select varchar(8000),
			@sumfunc varchar(100), 
			@pivot varchar(100), 
			@table varchar(100) 
AS

/* Original Script Written By Rob Volk @ SQLTeam.com*/

DECLARE @sql varchar(8000), @delim varchar(1)
--Added to increase the size of the query string--cwburke 10/09/01
DECLARE @sql2 varchar(8000)		--Second "pivot" sql string
DECLARE @BeginSelect varchar(8000)	--Initial Select statement passed in by user
DECLARE @EndSelect varchar(8000)	--Initial From statement passed in by user
DECLARE @LastPivot varchar(100)	--Last pivot read from ##pivot before Len(@sql) > 8000
DECLARE @Revsql varchar(8000)	--@sql Reversed
DECLARE @x int			--Charindex of "WHEN" in @RevSql
--End 10/09/01

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2 ')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' 
+ @pivot + ' Is Not Null Order By ' + @pivot)  --Add Order By Clause Here

SELECT @sql='', @sql2='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) ) 
WHEN 0 THEN '' ELSE '''' END 
FROM tempdb.information_schema.columns 
WHERE table_name='##pivot' AND column_name='pivot'


SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' + stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, 
			'CASE ' + @pivot + ' WHEN ' + @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ' , ' + char(13)
FROM ##pivot

--If @sql > 8000, then build 2nd query string 
If Len(@sql) >= 8000
Begin
	--Reverse the string
	Set @RevSql=Reverse(@sql)

	--Search for last occurence of WHEN
	Set @x = charindex(',', @RevSql)

	--Reverse the string again
	Set @LastPivot = Reverse(Left(@RevSql, @x -1))

	--Reset @sql to remove the imcomplete item
	Set @sql = Left(@Sql, Len(@sql) - (@x - 1))
	
	--Get the last pivot
	Set @LastPivot = Substring(@LastPivot, 4, charindex("' ", @LastPivot) - 4)

	--Pickup From Last read pivot and build @sql2
	Select @sql2 = @sql2 + '''' + convert(varchar(100), pivot) + ''' = ' + stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, 
			'CASE ' + @pivot + ' WHEN ' + @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ' , ' + char(13)
	FROM ##pivot
	WHERE pivot >= @LastPivot
End

DROP TABLE ##pivot

--If @sql2 exists, remove trailing comma
If len(@sql2) > 0
Begin
	SELECT @sql2=left(@sql2, len(@sql2)-4)
End
Else
Begin
	--Remove trailing comma from @sql if @sql2 is empty
	Select @sql=left(@sql, len(@sql)-1)
End

--Get Initial Select
Select @BeginSelect=Left(@select, charindex(' FROM ', @select)-1) + ', '
--Get Initial From
Select @EndSelect=Right(@select, Len(@select) - CharIndex(' FROM ', @select)+1)

--EXEC (@BeginSelect + @sql + @sql2 + ' ' + @EndSelect)
SET NOCOUNT ON
SET ANSI_WARNINGS ON