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.
Read more →
Here's a very common situation that is very easy to optimize and simplify, submitted via the mailbag:
Nate writes: Hey, I have a read a bunch of your stuff on your blog and you seem tobe right on the money.
Read more →
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.
Read more →
Time for another exciting edition of the mailbag! Maxime writes:
Hi,First of all, your class is really nice and it handles my problem really well.I would like to ask you something about the class.
Read more →
OK, so let's sum up my recent flury of crosstab / pivoting posts the past week or so:
Here's a dynamic way to do crosstabs in SQL Server But here's an alternate way to do this at the presentation layer in .
Read more →
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.
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 (or pivots) in SQL, such as:
Select CustomerID, SUM(CASE WHEN type = 'Land' THEN Amount ELSE 0 END) as Land, SUM(CASE WHEN type = 'Sea' THEN Amount ELSE 0 END) as Sea, SUM(CASE WHEN type = 'Air' THEN Amount ELSE 0 END) as Air FROM SomeTable GROUP BY CustomerID
Read more →