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 |