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