Codespct

When I needed a pivot table or Crosstab type of view in SQLServer, I was disapointed to not find anything resembling the MS Access Crosstab query. There are tools available, but I didn’t want to pay the kind of money they were asking and felt it necessary to learn how to do this myself.
Searching the web, Google, and posting to newsgroups paid little dividends. Mostly what I found was a lot of people having the same difficulty, but few answers. Finally, I started piecing together bits & pieces of information and came up with the following stored procedure.
Feel free to use this code, but please retain my name as a credit. Thank you.
/************************************************************
sp_JRMCrossTab – Dynamically creates a crosstab / pivot type of table.
Author : John MacIntyre
http://www.johnmacintyre.ca
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),
@RowFld VARCHAR(255),
@ColFld VARCHAR(255),
@ValFld VARCHAR(255),
@Filter VARCHAR(255),
@AggFunct VARCHAR(255)
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 We can test the sp_JRMCrossTab with the statement below. /********************** Test sp_JRMCrossTab - Simple example **********************/ exec sp_JRMCrossTab 'pubs.dbo.sales', 'stor_id', 'payterms', 'qty', NULL, 'sum' GO /********************** Test sp_JRMCrossTab - Example with a join **********************/ EXEC sp_JRMCrossTab 'pubs.dbo.sales AS s LEFT JOIN pubs.dbo.titles as t ON s.title_id=t.title_id', 'CAST(t.title AS varchar(25))', 'cast(month(ord_date) as varchar(2)) + ''/'' + cast(year(ord_date) as varchar(4))', 's.qty', '', 'sum' GO