Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

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.
UNPIVOT: Normalizing data on the fly

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,...

posted @ Wednesday, April 23, 2008 10:33 AM | Feedback (17) | Filed Under [ T-SQL CrossTabs / Pivoting Data Techniques SQL Server 2005 Report Writing SQL Server 2008 ]

Rewriting correlated sub-queries with CASE expressions

Here's a very common situation that is very easy to optimize and simplify, submitted via the mailbag.

posted @ Wednesday, January 09, 2008 8:42 AM | Feedback (3) | Filed Under [ CrossTabs / Pivoting Data Efficiency Joins/Relations ]

SQL 2005 PIVOT Operator (link)

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.

posted @ Thursday, July 12, 2007 9:24 AM | Feedback (2) | Filed Under [ CrossTabs / Pivoting Data SQL Server 2005 Links ]

Sorting Columns with the C# Pivot Function

Time for another exciting edition of the mailbag! This time: making an improvement to the C# Pivot function.

posted @ Friday, April 20, 2007 12:05 PM | Feedback (5) | Filed Under [ CrossTabs / Pivoting Data .NET (C# / VB) Code Library - C# / VB.NET ]

ASP and ADO Pivots -- Old School CrossTabs

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.

posted @ Sunday, May 15, 2005 9:26 PM | Feedback (4) | Filed Under [ CrossTabs / Pivoting Data ASP ]

.NET CrossTabs versus SQL Server CrossTabs

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...

posted @ Thursday, May 12, 2005 10:01 AM | Feedback (7) | Filed Under [ T-SQL CrossTabs / Pivoting Data .NET (C# / VB) Efficiency Report Writing Code Library - C# / VB.NET ]

.NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable

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...

posted @ Wednesday, May 11, 2005 3:49 PM | Feedback (23) | Filed Under [ T-SQL CrossTabs / Pivoting Data .NET (C# / VB) Code Library - C# / VB.NET ]

Another Dynamic SQL CrossTab Stored Procedure

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...

posted @ Monday, May 02, 2005 2:00 PM | Feedback (60) | Filed Under [ T-SQL CrossTabs / Pivoting Data Code Library - SQL ]

Keep those SQL Crosstabs flexible!

I recently helped someone with performing some simple cross-tabs in SQL ... read more...

posted @ Friday, October 24, 2003 4:40 PM | Feedback (1) | Filed Under [ T-SQL CrossTabs / Pivoting Data Techniques ]

Powered by:
Powered By Subtext Powered By ASP.NET