## Rows to Columns

### Transposing Many to One

This topic demonstrates how to collect and spread values from multiple rows under a single column, like those in VAL below

```GROUP_KEY  PIVOT_KEY  VAL
---------- ---------- ----------
G0         P0         a
G0         P0         b
G0         P0         (null)

G0         P1         A
G0         P1         BB
G0         P1         CCC

G0         P2         1
G0         P2         2222

G0         (null)     xyz
G0         (null)     (null)

G1         P0         a
G1         P0         b
G1         P0         c

G1         P2         1
G1         P2         2

(null)     P0         c

(null)     P1         C

(null)     (null)     (null)
```

over multiple columns and multiple rows, like this.

```GROUP_KEY  P0         P1         P2         UNASSIGNED
---------- ---------- ---------- ---------- ----------
G0         a          A          1          xyz
G0         b          BB         2222
G0                    CCC

G1         a                     1
G1         b                     2
G1         c

(null)     c          C
```

In this scenario there can be many VAL values for each distinct (GROUP_KEY, PIVOT_KEY) pair.

#### 11g PIVOT Solution

In Oracle 11g the PIVOT clause can be used as follows to produce a solution.

```column group_key null '(null)'

break on group_key duplicates skip 1

select
group_key ,
p0 ,
p1 ,
p2 ,
unassigned
from
( select
group_key ,
nvl( pivot_key, '%' ) as pivot_key ,
row_number()
over( partition by group_key, pivot_key order by val )
as row_num ,
val
from t3
) t3a pivot
( max(val)
for pivot_key in
(
'P0' as p0 ,
'P1' as p1 ,
'P2' as p2 ,
'%'  as unassigned
)
)
order by group_key, row_num ;
```
```GROUP_KEY  P0         P1         P2         UNASSIGNED
---------- ---------- ---------- ---------- ----------
G0         a          A          1          xyz
G0         b          BB         2222
G0                    CCC

G1         a                     1
G1         b                     2
G1         c

(null)     c          C
```

It is interesting to note that the ROW_NUM column does not need to appear in the SELECT clause for Oracle to implicitly group by it when PIVOT is used.

#### How it Works

The solution starts by creating a ROW_NUM column which assigns each VAL value a row number within each (GROUP_KEY, PIVOT_KEY) grouping. We also ensure PIVOT_KEY only contains non-null values in this step (this will be required later for the PIVOT IN clause to work properly).

```column val       null '(null)'
column row_num   print

break on pivot_key duplicates skip 1

select
group_key ,
nvl( pivot_key, '%' ) as pivot_key ,
ROW_NUMBER() OVER( PARTITION BY GROUP_KEY, PIVOT_KEY ORDER BY VAL ) AS ROW_NUM ,
val
from t3
order by group_key, pivot_key, val ;
```
```GROUP_KEY  PIVOT_KEY     ROW_NUM VAL
---------- ---------- ---------- ----------
G0         %                   1 xyz
G0         %                   2 (null)

G0         P0                  1 a
G0         P0                  2 b
G0         P0                  3 (null)

G0         P1                  1 A
G0         P1                  2 BB
G0         P1                  3 CCC

G0         P2                  1 1
G0         P2                  2 2222

G1         P0                  1 a
G1         P0                  2 b
G1         P0                  3 c

G1         P2                  1 1
G1         P2                  2 2

(null)     %                   1 (null)

(null)     P0                  1 c

(null)     P1                  1 C
```

From here the solution works the same as the MAX query described in the Aggregating Values (11g) topic, except that here we implicitly group by (GROUP_KEY, ROW_NUM) instead of just GROUP_KEY.

```break on group_key duplicates skip 1

select
group_key ,
row_num ,
p0 ,
p1 ,
p2 ,
unassigned
from
( select
group_key ,
nvl( pivot_key, '%' ) as pivot_key ,
row_number()
over( partition by group_key, pivot_key order by val )
as row_num ,
val
from t3
) t3a pivot
( max(val)
for pivot_key in
(
'P0' as p0 ,
'P1' as p1 ,
'P2' as p2 ,
'%'  as unassigned
)
)
order by group_key ;
```
```GROUP_KEY     ROW_NUM P0         P1         P2         UNASSIGNED
---------- ---------- ---------- ---------- ---------- ----------
G0                  1 a          A          1          xyz
G0                  2 b          BB         2222
G0                  3            CCC

G1                  1 a                     1
G1                  2 b                     2
G1                  3 c

(null)              1 c          C
```

#### Earlier Versions

For those who are not working with Oracle 11g the following query works in Oracle versions from 8i onward.

```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
( select
group_key ,
pivot_key,
row_number()
over( partition by group_key, pivot_key order by val )
as row_num ,
val
from t3
)
group by group_key, row_num
order by group_key, row_num ;
```
```GROUP_KEY  P0         P1         P2         UNASSIGNED
---------- ---------- ---------- ---------- ----------
G0         a          A          1          xyz
G0         b          BB         2222
G0                    CCC

G1         a                     1
G1         b                     2
G1         c

(null)     c          C
```

#### 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-12190.html]SQL Snippets: Rows to Columns - Transposing Many to One[/url]