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.