## Rows to Columns

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

#### Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

• [url=http://www.sqlsnippets.com/en/topic-12180.html]SQL Snippets: SQL Techniques Tutorials - Rows to Columns[/url]