Creat a Pivot Table with several options

CREATE PROCEDURE proCrossTabMulti
(
@strSELECT NVARCHAR(750), -- 'SELECT A,B'
@strFROM NVARCHAR(750), -- 'FROM atabletest INNERJOIN atbl1 ON atbl1.int1 = atabletest.int1'
@strWHERE NVARCHAR(750), -- 'WHERE (((A <> ''A'') AND (A <>''B'')) OR (B = ''C''))'
@strConvertField NVARCHAR(50),  -- Field name to put at top strGroupI  
@strTotals NVARCHAR(6) = 'NONE',  -- Totals BOTTOM, RIGHT, BOTH, NONE, ALL
@strFields NVARCHAR(250), --Fields to Calc int1,int1,int2
@strFieldsAggregate NVARCHAR(250)  -- sum,avg,count  Aggregates for above @strFields number of arguments must match 
   )
AS
/************************************************************************************************
*** Developed by Chad Bowen 8/15/01
*** Executes a crosstab query that is similiar to Access PIVOT command
*** Converts on multiple fields
************************************************************************************************/
DECLARE @strReturn NVARCHAR(75)
DECLARE @strSQL NVARCHAR(4000)
DECLARE @intPosition1 INTEGER
DECLARE @intPosition2 INTEGER
DECLARE @strValu1 NVARCHAR(75)
DECLARE @strValu3 NVARCHAR(75)
DECLARE @intPosition3 INTEGER
DECLARE @intPosition4 INTEGER

SELECT name from sysobjects where name = 'TopTable'
IF @@ROWCOUNT > 0
 BEGIN
DROP TABLE TopTable
 END
CREATE TABLE TopTable
    ( strTop NVARCHAR(75))

SET @strSQL = 'INSERT INTO TopTable '
SET @strSQL = @strSQL + 'SELECT DISTINCT ' + @strConvertField --  CAST(' + @strConvertField + ' as NVARCHAR)'
SET @strSQL = @strSQL + ' ' +  @strFROM 
SET @strSQL = @strSQL + ' ' +  @strWHERE
PRINT @strSQL

--INSERT INTO @TopTable 
EXEC sp_executesql @strSQL

SET @strSQL = ''
SET @strSQL = @strSelect

DECLARE intCursor CURSOR FOR
SELECT * FROM topTable
OPEN intCursor
FETCH NEXT FROM intCursor INTO @strReturn
WHILE @@FETCH_STATUS = 0
 BEGIN
SET @intPosition1 = 1
SET @intPosition3 = 1
SET @intPosition2 = LEN(@strFields)
SET @intPosition4 = LEN(@strFieldsAggregate)
WHILE @intPosition2 <> 0
 BEGIN
  SET @intPosition2 = CHARINDEX(',',@strFields,@intPosition1)
SET @intPosition4 = CHARINDEX(',',@strFieldsAggregate,@intPosition3)
IF @intPosition2 = 0
 BEGIN
SET @strValu1 = SUBSTRING(@strFields,@intPosition1,LEN(@strFields))
 END
ELSE
 BEGIN
SET @strValu1 = SUBSTRING(@strFields,@intPosition1,@intPosition2 - @intPosition1)
IF @intPosition2 = LEN(@strFields)
 BEGIN
SET @intPosition2 = 0
 END
 END
IF @intPosition4 = 0
 BEGIN
SET @strValu3 = SUBSTRING(@strFieldsAggregate,@intPosition3,LEN(@strFieldsAggregate))
 END
ELSE
 BEGIN
SET @strValu3 = SUBSTRING(@strFieldsAggregate,@intPosition3,@intPosition4 - @intPosition3)
IF @intPosition3 = LEN(@strFieldsAggregate)
  BEGIN
SET @intPosition4 = 0
  END
 END
SET @intPosition3 = @intPosition4 + 1
SET @intPosition1 = @intPosition2 + 1
IF ((@intPosition2 = 0) AND (@intPosition4 <> 0)) OR ((@intPosition4 = 0) AND (@intPosition2<>0))
  BEGIN
PRINT 'CONFLICTING # of Arguments'
RETURN
  END
SET @strReturn = LTRIM(RTRIM(@strReturn))
  SET @strSQL = @strSQL + ',' + @strValu3 + '(CASE '
  SET @strSQL = @strSQL + @strConvertField  + ' WHEN ' + CHAR(39) + @strReturn + CHAR(39)
  SET @strSQL = @strSQL + ' THEN ' + @strValu1 + ' ELSE NULL END) AS ['
SET @strSQL = @strSQL + @strReturn + '-' + @strValu3 + '-' + @strValu1 + ']'
IF LEN(@strSQL) >=3999
 BEGIN
PRINT 'SQL Was to long limited to 4000 Characters'
 PRINT @strSQL
RETURN
 END
 END
FETCH NEXT FROM intCursor into @strReturn
 END
CLOSE intCursor
DEALLOCATE intCursor
IF @strTotals = 'RIGHT' OR @strTotals = 'BOTH' OR @strTotals = 'ALL'
 BEGIN
SET @intPosition1 = 1
SET @intPosition3 = 1
SET @intPosition2 = LEN(@strFields)
SET @intPosition4 = LEN(@strFieldsAggregate)
WHILE @intPosition2 <> 0

 BEGIN
  SET @intPosition2 = CHARINDEX(',',@strFields,@intPosition1)
SET @intPosition4 = CHARINDEX(',',@strFieldsAggregate,@intPosition3)
IF @intPosition2 = 0
 BEGIN
SET @strValu1 = SUBSTRING(@strFields,@intPosition1,LEN(@strFields))
 END
ELSE
 BEGIN
SET @strValu1 = SUBSTRING(@strFields,@intPosition1,@intPosition2 - @intPosition1)
IF @intPosition2 = LEN(@strFields)
 BEGIN
SET @intPosition2 = 0
 END
 END
IF @intPosition4 = 0
 BEGIN
SET @strValu3 = SUBSTRING(@strFieldsAggregate,@intPosition3,LEN(@strFieldsAggregate))
 END
ELSE
 BEGIN
SET @strValu3 = SUBSTRING(@strFieldsAggregate,@intPosition3,@intPosition4 - @intPosition3)
IF @intPosition3 = LEN(@strFieldsAggregate)
  BEGIN
SET @intPosition4 = 0
  END
 END
SET @intPosition3 = @intPosition4 + 1
SET @intPosition1 = @intPosition2 + 1
IF ((@intPosition2 = 0) AND (@intPosition4 <> 0)) OR ((@intPosition4 = 0) AND (@intPosition2<>0))
  BEGIN
PRINT 'CONFLICTING # of Arguments'
RETURN
  END
SET @strSQL = @strSQL + ',' + @strValu3 + '(' + @strValu1 + ') AS ['
SET @strSQL = @strSQL + @strValu3 + '-' + @strValu1 + ']'
IF LEN(@strSQL) >=3999
 BEGIN
PRINT 'SQL Was to long limited to 4000 Characters'
 PRINT @strSQL
RETURN
 END
 END
 END
IF @strTotals = 'BOTTOM' OR @strTotals = 'BOTH'  OR @strTotals = 'ALL'
 BEGIN
SET @strSQL = @strSQL + ',' + 'GROUPING('
IF CHARINDEX ( ',', @strSELECT,0) > 0
 BEGIN
SET @strSQL = @strSQL + SUBSTRING(@strSELECT,8,CHARINDEX ( ',', @strSELECT,0)-8) + ') ''grp'''
 END
ELSE
 BEGIN
SET @strSQL = @strSQL + SUBSTRING(@strSELECT,8,LEN(@strSELECT)) + ') ''grp'''
 END
 END
SET @strSQL = @strSQL + ' ' + @strFROM
SET @strSQL = @strSQL + ' ' + @strWHERE
SET @strSQL = @strSQL + ' GROUP BY ' + SUBSTRING(@strSELECT,8, LEN(@strSelect) - 7)
IF @strTotals = 'BOTTOM' OR @strTotals = 'BOTH'
 BEGIN
SET @strSQL = @strSQL + ' WITH ROLLUP'
 END
IF @strTotals = 'ALL'
 BEGIN
SET @strSQL = @strSQL + ' WITH CUBE'
 END
IF LEN(@strSQL) >=3999
 BEGIN
PRINT 'SQL Was to long limited to 4000 Characters'
 PRINT @strSQL
RETURN
 END

PRINT @strSQL 

SELECT * FROM TopTable
PRINT LEN(@strSQL)
EXEC sp_executesql @strSQL

RETURN


GO