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