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.
Everyone seems to want to "pivot" or "cross tab" data, but knowing how to do the opposite is equally important. In fact, I would argue that the skill of "unpivoting" data is more useful and more important and more relevant to a SQL programmer, since pivoting results in denormalized data, while unpivoting can transform non-normalized data into a normalized result set. We all know that there's lots of bad databases designs out there, so this can be a handy technique to know.
Of course, even a well designed, fully normalized database can still benefit from "unpivoting" from time to time,...
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...
In my last post, I spoke briefly about how I felt that in general crosstabbing data is something that a presentation layer should do and not the database. Consider the result of a crosstab operation -- the columns returned will vary depending on the data. There is pretty much no further manipulation you can do with that result in T-SQL; in the relational database world, the column names of our database objects should be constants and not continually changing as the data changes. Also, in T-SQL there is no easy way to dynamically pivot data, and even doing it with...
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...