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
