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:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
DECLARE @MySource TABLE(General VARCHAR(100), SoldierType VARCHAR(100), Number INT) INSERT INTO @MySource (General, SoldierType, Number) VALUES ('Scipio', 'Archers', 2000), ('Scipio', 'Footmen', 1500), ('Scipio', 'Footmen', 4500), ('Hannibal', 'Footmen', 5000), ('Scipio', 'Cavalry', 750), ('Hannibal', 'Elephants', 35), ('Hannibal', 'Archers', 1250) /* Select unpivoted source data */ SELECT * FROM @MySource /* Pivot the source data */ SELECT "General", "Footmen", "Archers", "Cavalry", "Elephants" FROM (SELECT "General", "SoldierType", "Number" FROM @MySource) MySrc PIVOT ( SUM("Number") FOR "SoldierType" IN ("Footmen", "Archers", "Cavalry", "Elephants") ) AS PivotTable |
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.
Enjoy!