Dynamic Cross-Tabs/Pivot Tables - SQL Server Information at SQLTeam.com

Dynamic Cross-Tabs/Pivot Tables - SQL Server Information at SQLTeam.com

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'


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


Related Articles:
Efficient and Dynamic Server-Side Paging with T-SQL on 3/23/2004 rated 5.0 (1)
Introduction to Dynamic SQL (Part 1) on 6/20/2001 rated 4.4 (7)
Introduction to Dynamic SQL (Part 2) on 6/27/2001 rated 4.5 (12)
Dynamic CrossTabs on 9/23/2001
Implementing a Dynamic WHERE Clause on 1/14/2001 rated 4.8 (12)



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!

td.rob {color: #343399; background-color: #eff7fe; font-family: Arial; font-size: 10pt; padding-left: 7px; padding-right: 7px; text-align: right;}
td.robl {color: #343399; background-color: #eff7fe; font-family: Arial; font-size: 10pt; padding-left: 7px; padding-right: 7px;}
td.robhead {color: #343399; background-color: #a6cedd; font-family: Arial; font-size: 10pt; font-weight: bold; padding-left: 7px; padding-right: 7px;}
td.rob1 {color: #AAFFAA; background-color: #333388; font-family: Trebuchet, 'Arial Narrow', Arial; font-size: 10pt;}
td.robhead1 {color: #FFFFFF; background-color: #AA0000; font-family: Arial; font-size: 10pt; font-weight: bold;}
td.cap {text-align: center; border-left: thin solid black; border-top: thin solid black; font-weight: bold; padding-left: 10px; padding-right: 10px;}
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: