Search my blog

Some of my readers



 

One of the features of MS Access is the TRANSFORM statement, used to create cross-tabs/pivot tables. It does all of the work of dynamically generating the cross-tabulation and the summary calculations.

T-SQL unfortunately doesn't have this statement, so you're stuck using complicated SQL commands, expensive 3rd party products, or exotic OLAP to make pivot tables...or you can use the following procedure to dynamically create them!

This example would be executed on the Northwind sample database.

SQL Code

EXECUTE crosstab 'SELECT LastName FROM Employees INNER JOIN Orders ON (Employees.EmployeeID=Orders.EmployeeID) GROUP BY LastName', 'count(lastname)', 'Year(OrderDate)', 'Orders'

Results

LastName 1996 1997 1998
Buchanan 11 18 13
Leverling 18 71 38
Fuller 16 41 39
Peacock 31 81 44
Callahan 19 54 31
Suyama 15 33 19
King 11 36 25
Dodsworth 5 19 19
Davolio 26 55 42


For more details see the following article:
http://www.sqlteam.com/item.asp?ItemID=2955

I adjusted the Stored Procedure so that it could handle data with single quotes for field names.

CREATE PROCEDURE crosstab
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
AS

DECLARE @sql varchar(8000), @delimStart varchar(1), @delimEnd varchar(1), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

select @delimStart = '['
select @delimEnd = ']'

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')

SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '[' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + @DelimStart + convert(varchar(100), pivot) + @DelimEnd + ' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)

SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
print @select
EXEC (@select)
SET ANSI_WARNINGS ON 

 

posted on Monday, September 05, 2005 6:52 PM | Filed Under [ SQL Server ]