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