Pivot operators group data from several related columns into a single column by creating additional rows. You can use a pivot operator when a number of column headings are related closely enough that they make sense as values in a single column. The result table contains fewer columns and more rows than the input table.
Examples
You want to use the pivot operator on twelve columns of monthly sales data for two stores. You choose the months as pivot columns. The input table looks like this:
STORE M1 M2 M3 M4 M5 M6 M7 M8 M9 M10 M11 M12 A 20 25 30 32 27 14 16 07 28 23 13 46 B 19 29 42 37 22 12 -- 26 47 31 15 40
Note how the months have become values in a single column in the result table:
STORE MONTH SALES A M1 20 A M2 25 A M3 30 A M4 32 A M5 27 A M6 14 A M7 16 A M8 23 A M9 28 A M10 23 A M11 13 A M12 46 B M1 19 B M2 29 B M3 42 B M4 42 B M5 22 B M6 12 B M7 -- B M8 26 B M9 47 B M10 31 B M11 15 B M12 40
In a more complex example, a table contains sales data for a store with columns for the three months of each quarter. The months can be converted to twelve values in a single month column.
STORE QTR M1 M2 M3 A Q1 20 25 30 A Q2 32 27 14 B Q1 19 29 42 B Q2 37 22 12
The pivot operator creates a row in the result for each distinct combination of store, month, and quarter values.
STORE MO QTR SALES A M1 Q1 20 A M2 Q1 25 A M3 Q1 30 A M1 Q2 32 A M2 Q2 27 A M3 Q2 14 B M1 Q1 19 B M2 Q1 29 B M3 Q1 42 B M1 Q2 37 B M2 Q2 22 B M3 Q2 12