Dynamic Cross-Tabs/Pivot TablesBy Rob Volk on 12 March 2001 | Tags: Dynamic SQL IMHO, the best feature 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! I got the idea from this question, asking how to "undo" a pivot table, and then I started working on how to create them in T-SQL. There are numerous ways of doing pivot tables, and this site has several examples (and lots of other cool stuff). The standard method uses a CASE statement, with one CASE for each pivot value (the column headings created by cross-tabbing the pivot column). The greatest shortcoming is finding a way to handle an unknown or changing number of pivot values. Obviously you have to know these values beforehand, and you must add a CASE for each new, distinct value inserted into the pivot column. The code listed below will do all of the work for you: CREATE PROCEDURE crosstab @select varchar(8000), @sumfunc varchar(100), @pivot varchar(100), @table varchar(100) AS DECLARE @sql varchar(8000), @delim varchar(1) SET NOCOUNT ON SET ANSI_WARNINGS OFF 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 + '''' + convert(varchar(100), pivot) + ''' = ' + 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 + ' ') EXEC (@select) SET ANSI_WARNINGS ON Yeah, I know, you want to know how it works :) I won't go into detail on the mechanics of the code. Let's just say that if you can follow it, feel free to play with it; if not, DON'T TOUCH IT! The syntax for the procedure call is below, followed by a description of each parameter:
I'll list some cross-tab settings and the results. Here's two you can run in the pubs database: EXECUTE crosstab 'select title from titles inner join sales on (sales.title_id=titles.title_id) group by title', 'sum(qty)','stor_id','stores'
EXECUTE crosstab 'select pub_name, count(qty) as orders, sum(qty) as total from sales inner join titles on (sales.title_id=titles.title_id) right join publishers on (publishers.pub_id=titles.pub_id) group by pub_name', 'sum(qty)','type','titles'
Here's one that will run in Northwind: EXECUTE crosstab 'SELECT LastName FROM Employees INNER JOIN Orders ON (Employees.EmployeeID=Orders.EmployeeID) GROUP BY LastName', 'count(lastname)', 'Year(OrderDate)', 'Orders'
Some things to look out for:
I'm spending some time working on enhancements to this procedure (for a follow-up article), to allow things like subqueries, multiple pivoting columns, non-aggregate values (e.g. - show the store with the highest sales each month, pivoted by book type). I would love to hear from anyone who has suggestions or solutions on how to improve this code. Enjoy! -robvolk |
- Advertisement - |