Rows to Columns

Transposing One to One

This topic demonstrates how to take values like those under the VAL column below

```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 arrange them in a matrix like this (also known as a "crosstab report" or "pivot table").

```GROUP_KEY  P0         P1         P2         UNASSIGNED
---------- ---------- ---------- ---------- ----------
G0         a          A          1          xyz
G1         b          B
(null)     c                     3
```

In this scenario there is only one VAL value for each distinct (GROUP_KEY, PIVOT_KEY) pair.

11g PIVOT Solution

A PIVOT query like the ones presented in Aggregating Values (11g) can be used with the aggregate functions MAX or MIN to transpose rows to columns

```column group_key null '(null)'

select
group_key ,
p0 ,
p1 ,
p2 ,
unassigned
from
( select group_key, nvl(pivot_key,'%') as pivot_key, val from t1 ) pivot
( MAX(val)
for pivot_key in
(
'P0' as p0 ,
'P1' as p1 ,
'P2' as p2 ,
'%'  as unassigned
)
)
order by
group_key
;
```
```GROUP_KEY  P0         P1         P2         UNASSIGNED
---------- ---------- ---------- ---------- ----------
G0         a          A          1          xyz
G1         b          B
(null)     c                     3
```

All Versions

A slightly more compact solution that works in any version of Oracle can be constructed using the MAX query presented in Aggregating Values.

```select
group_key ,
max( decode( pivot_key, 'P0', val, null ) ) as p0 ,
max( decode( pivot_key, 'P1', val, null ) ) as p1 ,
max( decode( pivot_key, 'P2', val, null ) ) as p2 ,
max( decode( pivot_key, null, val, null ) ) as unassigned
from t1
group by group_key
order by group_key
;
```
```GROUP_KEY  P0         P1         P2         UNASSIGNED
---------- ---------- ---------- ---------- ----------
G0         a          A          1          xyz
G1         b          B
(null)     c                     3
```

Note that MAX could be replaced with MIN and the results would still be the same since the DECODE logic ensures there is only one non-null value per group per column.

```select
group_key ,
MIN( decode( pivot_key, 'P0', val, null ) ) as p0 ,
MIN( decode( pivot_key, 'P1', val, null ) ) as p1 ,
MIN( decode( pivot_key, 'P2', val, null ) ) as p2 ,
MIN( decode( pivot_key, null, val, null ) ) as unassigned
from t1
group by group_key
order by group_key
;
```
```GROUP_KEY  P0         P1         P2         UNASSIGNED
---------- ---------- ---------- ---------- ----------
G0         a          A          1          xyz
G1         b          B
(null)     c                     3
```