This section presents techniques for transforming rows into columns. Three different types of transformations are covered - aggregating, transposing, and distributing.
Aggregating
Aggregating refers to transformations that take values like these
GROUP_KEY PIVOT_KEY VAL ---------- ---------- ---------- G0 P0 10 G0 P1 5 G0 P1 3 G0 P2 20 G0 P2 6 G1 P1 9 G1 P1 (null) G1 P2 (null) G1 P2 5 G2 (null) 4 G2 (null) 7 G2 (null) 10 (null) P0 15 (null) P2 (null) (null) (null) (null)
and aggregate them into a crosstab matrix like this.
GROUP_KEY P0_SUM P1_SUM P2_SUM UNASSIGNED_SUM ---------- ---------- ---------- ---------- -------------- G0 10 8 26 G1 9 5 G2 21 (null) 15
This transformation is commonly referred to as "pivoting" or creating a "crosstab" report.
Transposing
Transposing refers to transformations that take values like these
GROUP_KEY PIVOT_KEY VAL ---------- ---------- ---------- G0 P0 a G0 P1 A G0 P2 1 G0 (null) xyz G1 P0 b G1 P1 B G1 P2 (null) (null) P0 c (null) P2 3 (null) (null) (null)
and rearrange them into a matrix like this.
GROUP_KEY P0 P1 P2 UNASSIGNED ---------- ---------- ---------- ---------- ---------- G0 a A 1 xyz G1 b B (null) c 3
This transformation is also commonly referred to as "pivoting" or creating a "crosstab" report.
Distributing
Distributing refers to transformations that take values like these
GROUP_KEY VAL ---------- ---------- G8 a G8 b G8 c G8 d G8 e G8 f G8 g G8 h
and rearrange them over an arbitrary number of columns like as in result, which spreads values across three columns ordered from left to right
GROUP_KEY COL_0 COL_1 COL_2 ---------- ---------- ---------- ---------- G8 a b c G8 d e f G8 g h
or this result, which spreads values across two columns ordered from top to bottom (aka "newspaper column style").
GROUP_KEY COL_0 COL_1 ---------- ---------- ---------- G8 a e G8 b f G8 c g G8 d h
Tags
| Crosstab | Pivot Query |
| Crosstab Query | Pivot Table |
| Pivot | Print Rows Horizontally |
| Pivoting | Print Rows Across Instead of Down |
| Rotate Rows |