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