## Rows to Columns

### Distributing Top to Bottom - 2

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

```GROUP_KEY  VAL
---------- ----------
G4         a
G4         b
G4         c
G4         d

G7         a
G7         b
G7         c
G7         d
G7         e
G7         f
G7         g
```

across multiple columns, rows, and groups like this.

```GROUP_KEY  COL_0      COL_1      COL_2
---------- ---------- ---------- ----------
G4         a          c
G4         b          d

G7         a          d          g
G7         b          e
G7         c          f
```

Unlike prior topics in this section, here we deal with a base table that has no PIVOT_KEY column. Values will be distributed evenly from top to bottom, then left to right across an arbitrary number of columns (this is sometimes referred to as a "newspaper column" style). Empty cells will be placed as far right as possible (unlike the solution in Distributing Top to Bottom - 1 where empty cells were distributed evenly).

#### 11g PIVOT Solution

```break on group_key duplicates skip 1

select
group_key ,
col_0 ,
col_1 ,
col_2
from
( select
group_key ,
pivot_key ,
row_number() over( partition by group_key, pivot_key order by val )
as row_num ,
val
from
( select
group_key ,
trunc
( ( row_number() over( partition by group_key order by val ) - 1 ) /
( trunc
( ( count(*) over
( partition by group_key
order by 1 rows between unbounded preceding and unbounded following
) - 1
) / 3
) + 1
)
) as pivot_key ,
val
from t5
) t5a
) t5b pivot
( max(val)
for pivot_key in
(
0 as col_0 ,
1 as col_1 ,
2 as col_2
)
)
order by group_key, row_num ;
```
```GROUP_KEY  COL_0      COL_1      COL_2
---------- ---------- ---------- ----------
G0

G1         a

G2         a          b

G3         a          b          c

G4         a          c
G4         b          d

G5         a          c          e
G5         b          d

G6         a          c          e
G6         b          d          f

G7         a          d          g
G7         b          e
G7         c          f

G8         a          d          g
G8         b          e          h
G8         c          f
```

Note how the results for groups G4 and G7 differ from those produced in the Distributing Top to Bottom - 1 topic.

With two columns instead of three the query would look like this.

```select
group_key ,
col_0 ,
col_1
-- this line deleted
from
( select
group_key ,
pivot_key ,
row_number() over( partition by group_key, pivot_key order by val )
as row_num ,
val
from
( select
group_key ,
trunc
( ( row_number() over( partition by group_key order by val ) - 1 ) /
( trunc
( ( count(*) over
( partition by group_key
order by 1 rows between unbounded preceding and unbounded following
) - 1
) / 2 -- use "2" here
) + 1
)
) as pivot_key ,
val
from t5
) t5a
) t5b pivot
( max(val)
for pivot_key in
(
0 as col_0 ,
1 as col_1
-- this line deleted
)
)
order by group_key, row_num ;
```
```GROUP_KEY  COL_0      COL_1
---------- ---------- ----------
G0

G1         a

G2         a          b

G3         a          c
G3         b

G4         a          c
G4         b          d

G5         a          d
G5         b          e
G5         c

G6         a          d
G6         b          e
G6         c          f

G7         a          e
G7         b          f
G7         c          g
G7         d

G8         a          e
G8         b          f
G8         c          g
G8         d          h
```

The sort order can be controlled by changing the ORDER BY clauses in the analytic function arguments.

```select
group_key ,
col_0 ,
col_1 ,
col_2
from
( select
group_key ,
pivot_key ,
row_number() over( partition by group_key, pivot_key ORDER BY VAL DESC )
as row_num ,
val
from
( select
group_key ,
trunc
( ( row_number() over( partition by group_key ORDER BY VAL DESC ) - 1 ) /
( trunc
( ( count(*) over
( partition by group_key
order by 1 rows between unbounded preceding and unbounded following
) - 1
) / 3
) + 1
)
) as pivot_key ,
val
from t5
) t5a
) t5b pivot
( max(val)
for pivot_key in
(
0 as col_0 ,
1 as col_1 ,
2 as col_2
)
)
order by group_key, row_num ;
```
```GROUP_KEY  COL_0      COL_1      COL_2
---------- ---------- ---------- ----------
G0

G1         a

G2         b          a

G3         c          b          a

G4         d          b
G4         c          a

G5         e          c          a
G5         d          b

G6         f          d          b
G6         e          c          a

G7         g          d          a
G7         f          c
G7         e          b

G8         h          e          b
G8         g          d          a
G8         f          c
```

#### How it Works

The ROW_NUM and PIVOT_KEY values created by the inline views are the key to this solution.

```break on pivot_key duplicates skip 1

select
group_key ,
pivot_key ,
row_number() over( partition by group_key, pivot_key order by val )
as row_num ,
val
from
( select
group_key ,
trunc
( ( row_number() over( partition by group_key order by val ) - 1 ) /
( trunc
( ( count(*) over
( partition by group_key
order by 1 rows between unbounded preceding and unbounded following
) - 1
) / 3
) + 1
)
) as pivot_key ,
val
from t5
where group_key = 'G8'
) t5a
order by group_key, pivot_key, row_num ;
```
```GROUP_KEY   PIVOT_KEY    ROW_NUM VAL
---------- ---------- ---------- ----------
G8                  0          1 a
G8                  0          2 b
G8                  0          3 c

G8                  1          1 d
G8                  1          2 e
G8                  1          3 f

G8                  2          1 g
G8                  2          2 h
```

Once the PIVOT_KEY and ROW_NUM columns have been created, VAL values are simply distributed and collapsed with a PIVOT clause the same way they were in the Transposing M:1 topic.

#### Earlier Versions

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

```break on group_key duplicates skip 1

select
group_key ,
max( decode( pivot_key, 0, val, null ) ) col_0 ,
max( decode( pivot_key, 1, val, null ) ) col_1 ,
max( decode( pivot_key, 2, val, null ) ) col_2
from
( select
group_key ,
pivot_key ,
row_number() over( partition by group_key, pivot_key order by val )
as row_num ,
val
from
( select
group_key ,
trunc
( ( row_number() over( partition by group_key order by val ) - 1 ) /
( trunc
( ( count(*) over
( partition by group_key
order by 1 rows between unbounded preceding and unbounded following
) - 1
) / 3
) + 1
)
) as pivot_key ,
val
from t5
) t5a
) t5b
group by group_key, row_num
order by group_key, row_num ;
```
```GROUP_KEY  COL_0      COL_1      COL_2
---------- ---------- ---------- ----------
G0

G1         a

G2         a          b

G3         a          b          c

G4         a          c
G4         b          d

G5         a          c          e
G5         b          d

G6         a          c          e
G6         b          d          f

G7         a          d          g
G7         b          e
G7         c          f

G8         a          d          g
G8         b          e          h
G8         c          f
```

#### 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-12192.html]SQL Snippets: Rows to Columns - Distributing Top to Bottom - 2[/url]