This is an old revision of the document!
Pivot
Creates a matrix table using values in one column as column labels, and values in another column as the matrix column's values. This transformation is opposite to Unpivot.
Example: Winter Olympics medals
| Country | Season | Medals |
|---|---|---|
| Canada | winter 2010 | 26 |
| United States | winter 2010 | 37 |
| Norway | winter 2010 | 23 |
| Germany | winter 2010 | 30 |
| Canada | winter 2014 | 25 |
| United States | winter 2014 | 28 |
| Norway | winter 2014 | 26 |
| Germany | winter 2014 | 19 |
Pivot on columns [Season] and [Medals]. Aggregation - Any.
Result:
| Country | winter 2010 | winter 2014 |
|---|---|---|
| Canada | 26 | 25 |
| United States | 37 | 28 |
| Norway | 23 | 26 |
| Germany | 30 | 19 |
Note that grouping works implicitly in the "Pivot" action. Groups are formed by combination of values in other columns that not labels or data. Values in a group are aggregated into 1 row. For instance in the example above, column [Country] was used for implicit grouping, because two other columns, [Season] and [Medals], were used for labels and data respectively.
If a pivoted dataset has only two columns, one of which is used for data, and the other for labels), then the resulting dataset will always have only 1 row.
See also