Creat a Pivot Table with several options

,

Create a pivot Table similiar to Microsoft Access Pivot Command, Specify if there should be totals at the bottom, right or both..
Uses a temporary table to hold the values..
Bad part is the SQL that is generated can have no more than
4000 Characters..
If not using any NVARCHAR characters, could make the sql up to 8000 Characters.

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

Rate

Share

Share

Rate