July 22, 2004 at 6:47 am
Is there a way to specify a table, pivot field, pivot value and create a crosstab, much like the transform/pivot function of jet sql?
There was a posting this week that dealt with trouble shooting a hard coded crosstab to remove blank rows.
Someone responded with a question about writing a dynamic crosstab. I have a function in vbscript that does it for my asp pages but have been unable to figure it out in T-SQL. If anyone has one or is interested, please post. I envision feeding in table or view name,columns to group,pivot column,value column,aggregate function and returning a result table. It doesn't seem like a temp table is necessary, but rather running a query to get column values and then building the case statement for each of those values.
July 23, 2004 at 8:44 am
Hello williamhoos,
A quite good generic stored procedure is at this URL:
http://www.johnmacintyre.ca/%5Ccodespct.asp
Here is the complete code, with a slight annotation on my part:
/************************************************************
sp_JRMCrossTab - Dynamically creates a crosstab / pivot type of table.
Author : John MacIntyre
Copyright 2002 JohnMacIntyre.ca © All rights reserved.
Please use and modify as you like, but please keep my name and URL included.
************************************************************/
CREATE PROC sp_JRMCrossTab
@Table VARCHAR(255), -- Table or view name
@RowFld VARCHAR(255), -- Row field or field list
@ColFld VARCHAR(255), -- The pivot column
@ValFld VARCHAR(255), -- The value field
@Filter VARCHAR(255), -- The where clause
@AggFunct VARCHAR(255) -- The aggrate function.
AS
BEGIN
DECLARE @SQL AS VARCHAR(8000)
DECLARE @CursorSQL AS VARCHAR(8000)
DECLARE @ColVal AS VARCHAR(1024)
DECLARE @NewCol AS VARCHAR(2048)
/* get col hdrs */
SET @CursorSQL = 'DECLARE FldCursor CURSOR FAST_FORWARD FOR '
+ 'SELECT ' + @ColFld + ' FROM ' + @Table + ' GROUP BY '
+ @ColFld + ' ORDER BY ' + @ColFld
EXEC( @CursorSQL )
OPEN FldCursor
/* traverse col hdrs, generating SQL for table */
SET @SQL = 'SELECT ' + @RowFld
FETCH NEXT FROM FldCursor INTO @ColVal
WHILE 0 = @@FETCH_STATUS
BEGIN
SET @NewCol = ', ' + @AggFunct + '(CASE ' + @ColFld + ' WHEN '''
+ @ColVal + ''' THEN ' + @ValFld + ' ELSE NULL END) AS [' + @ColVal + ']'
SET @SQL = @SQL + @NewCol
FETCH NEXT FROM FldCursor INTO @ColVal
END
CLOSE FldCursor
DEALLOCATE FldCursor
/* finish SQL */
SET @SQL = @SQL + ' FROM ' + @Table
IF 0 < LEN( @Filter)
SET @SQL = @SQL + ' WHERE ' + @Filter
SET @SQL = @SQL + ' GROUP BY ' + @RowFld + ' ORDER BY ' + @RowFld
/* run it! */
/* PRINT @SQL */
EXEC( @SQL)
END
GO
Bob Monahon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply