Lots of ways to UNPIVOT a table

Leave a comment

August 21, 2013 by Kenneth Fisher

I recently read an awesome post on http://www.stackexchange.com questioning how to change columns to rows ie UNPIVOT. One answer demonstrated 4 different ways to unpivot a table including your basic UNPIVOT statement, CROSS APPLY, CROSS APPLY with VALUES and a dynamic SQL version for when you have a large number of columns.

The second answer demonstrated how to do the UNPIVOT using XQuery. The XQuery method also lets you UNPIVOT a large number of columns without naming them explicitly. There was a lot of discussion as to the pros and cons to this method in the comments, but I’m a firm believer in knowing all the methods you can (and all of the pros and cons) regardless of whether or not you plan on using them.

Now if only I could find as good a list of multiple method’s for PIVOTing a table.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,753 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: