CrossTabs / Pivoting Data
Everything you always wanted to know about crosstabbing data (summarizing multiple rows into multiple columns) but were afraid to ask!
The key to success is to remember: This is a presentation issue, not a database issue. Even though I show a few ways to get this done using T-SQL, that doesn't mean that you should it in general; most times, it is much more efficient and much easier to crosstab your data at the presentation layer.
Here's a very common situation that is very easy to optimize and simplify, submitted via the mailbag.
There's two interesting posts over at the MSDN blogs from Craig Freedman about the new PIVOT operator in SQL 2005. First, he gives a nice overview of the operator and how to use it, and then he follows it up with a performance analysis as well. A good read, check it out.
Time for another exciting edition of the mailbag! This time: making an improvement to the C# Pivot function.
Jumping back a bit, I thought I'd also post a bit of code for those still using good old ASP and/or ADO. This is a function writen in VBScript that works much in the same way as the GetRows() method of an ADO recordset -- it returns the entire contents of the recordset in a 2-dimensional array. The difference with this one, of course, is that it will also do a pivot for you.
As promised in my last post, here is some performance testing to help you determine the performance benefits (if any) of performing your crosstabs at the presentation or code layer, as opposed to forcing SQL Server to do this.
read more...
First off, before going any further make sure you have read the hall of fame SQLTeam article by Rob Volk on generating crosstab results using a flexible, dynamic stored procedure that has been viewed over 100,000 times!
read more...
I recently helped someone with performing some simple cross-tabs in SQL ... read more...