How to rotate table (turn rows into columns) in MS SQL

Using PIVOT and UNPIVOT
http://msdn.microsoft.com/en-us/library/ms177410.aspx