SQL Snippets ™: Tutorials for Oracle Developers

This topic demonstrates how to take values like those under the VAL column below

GROUP_KEY  PIVOT_KEY  VAL
---------- ---------- ----------
G0         P0         a
G0         P1         A
G0         P2         1
G0         (null)     xyz

G1         P0         b
G1         P1         B
G1         P2         (null)

(null)     P0         c
(null)     P2         3
(null)     (null)     (null)
 

and arrange them in a matrix like this (also known as a "crosstab report" or "pivot table").

GROUP_KEY  P0         P1         P2         UNASSIGNED
---------- ---------- ---------- ---------- ----------
G0         a          A          1          xyz
G1         b          B
(null)     c                     3
 

In this scenario there is only one VAL value for each distinct (GROUP_KEY, PIVOT_KEY) pair.

11g PIVOT Solution

A PIVOT query like the ones presented in Aggregating Values (11g) can be used with the aggregate functions MAX or MIN to transpose rows to columns

column group_key null '(null)'

select
  group_key ,
  p0 ,
  p1 ,
  p2 ,
  unassigned
from
  ( select group_key, nvl(pivot_key,'%') as pivot_key, val from t1 ) pivot
    ( MAX(val)
      for pivot_key in
        (
          'P0' as p0 ,
          'P1' as p1 ,
          'P2' as p2 ,
          '%'  as unassigned
        )
    )
order by
  group_key
;
 

GROUP_KEY  P0         P1         P2         UNASSIGNED
---------- ---------- ---------- ---------- ----------
G0         a          A          1          xyz
G1         b          B
(null)     c                     3
 

All Versions

A slightly more compact solution that works in any version of Oracle can be constructed using the MAX query presented in Aggregating Values.

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 t1
group by group_key
order by group_key
;
 

GROUP_KEY  P0         P1         P2         UNASSIGNED
---------- ---------- ---------- ---------- ----------
G0         a          A          1          xyz
G1         b          B
(null)     c                     3
 

Note that MAX could be replaced with MIN and the results would still be the same since the DECODE logic ensures there is only one non-null value per group per column.

select
  group_key ,
  MIN( decode( pivot_key, 'P0', val, null ) ) as p0 ,
  MIN( decode( pivot_key, 'P1', val, null ) ) as p1 ,
  MIN( decode( pivot_key, 'P2', val, null ) ) as p2 ,
  MIN( decode( pivot_key, null, val, null ) ) as unassigned
from t1
group by group_key
order by group_key
;
 

GROUP_KEY  P0         P1         P2         UNASSIGNED
---------- ---------- ---------- ---------- ----------
G0         a          A          1          xyz
G1         b          B
(null)     c                     3