Pivot vs Unpivot

When mnemonics and learning by heart fail: write a blog post! So here is my own personal “which is which” reference post about the Pivot and Unpivot operations. First things first:


Example of the pivot and unpivot operation


Using the great Scipio Africanus and Hannibal as puppets, the above example shows the two operations in their most basic form. The pivot operation would be along these lines in T-SQL:

The query above is also available as a SQL Fiddle, to toy around with it some more, as there are many more complex queries possible with these operations.