This topic demonstrates how to take values from multiple rows under a single column, like those in VAL below
GROUP_KEY PIVOT_KEY VAL ---------- ---------- ---------- G0 P0 10 G0 P1 5 G0 P1 3 G0 P2 20 G0 P2 6 G1 P1 9 G1 P1 (null) G1 P2 (null) G1 P2 5 G2 (null) 4 G2 (null) 7 G2 (null) 10 (null) P0 15 (null) P2 (null) (null) (null) (null)
and display aggregate results in a matrix, like this one which displays VAL sums in each cell.
GROUP_KEY P0_SUM P1_SUM P2_SUM UNASSIGNED_SUM ---------- ---------- ---------- ---------- -------------- G0 10 8 26 G1 9 5 G2 21 (null) 15
Non-null Pivot Keys
For the non-null pivot keys (P0, P1, and P2) the result is easily obtained using the SELECT command's PIVOT clause, which was first introduced in Oracle 11g for exactly these types of queries.
select
group_key ,
p0_sum ,
p1_sum ,
p2_sum
from
t2 pivot
( sum(val)
for pivot_key in
(
'P0' as p0_sum ,
'P1' as p1_sum ,
'P2' as p2_sum
)
)
order by
group_key
;
GROUP_KEY P0_SUM P1_SUM P2_SUM ---------- ---------- ---------- ---------- G0 10 8 26 G1 9 5 G2 (null) 15
Note how the query does not use a GROUP BY clause even though it includes the aggregate function SUM(). This is because with pivot queries Oracle implicitly groups by any column not in the PIVOT clause, e.g. GROUP_KEY, plus each value specified in the IN clause, e.g. 'P0', 'P1', 'P2'.
NULL Pivot Keys
To generate the UNASSIGNED_SUM column for values with null pivot keys our first inclination would be to use syntax such as this
select
group_key ,
unassigned_sum
from
t2 pivot
( sum(val)
for pivot_key in
( NULL AS UNASSIGNED_SUM )
)
order by
group_key
;
GROUP_KEY UNASSIGNED_SUM ---------- -------------- G0 G1 G2 (null)
but, as typically happens with nulls, the solution is not so easy. As we can see the null VAL values in our table are not considered a match to the null expression in the PIVOT IN clause.
Our next attempt proves equally fruitless.
select
group_key ,
unassigned_sum
from
t2 pivot
( sum(val)
for NVL(PIVOT_KEY,'%') in
( '%' as unassigned_sum )
)
order by
group_key
;
for NVL(PIVOT_KEY,'%') in
*
ERROR at line 7:
ORA-01738: missing IN keyword
The error is caused by the fact that the FOR clause only accepts columns, not expressions.
To generate meaningful results we need to resort to a little inline view ugliness.
select
group_key ,
p0_sum ,
p1_sum ,
p2_sum ,
unassigned_sum
from
( SELECT GROUP_KEY, NVL(PIVOT_KEY,'%') AS PIVOT_KEY, VAL FROM T2 ) pivot
( sum(val)
for pivot_key in
(
'P0' as p0_sum ,
'P1' as p1_sum ,
'P2' as p2_sum ,
'%' as unassigned_sum
)
)
order by
group_key
;
GROUP_KEY P0_SUM P1_SUM P2_SUM UNASSIGNED_SUM ---------- ---------- ---------- ---------- -------------- G0 10 8 26 G1 9 5 G2 21 (null) 15
Multiple Aggregates
If needed we can compute multiple aggregates, e.g. SUM and MAX, easily using PIVOT.
select
group_key ,
p1_sum ,
p1_max ,
p2_sum ,
p2_max
from
t2 pivot
( SUM(VAL) AS SUM ,
MAX(VAL) AS MAX
for pivot_key in
(
'P1' as p1 ,
'P2' as p2
)
)
order by
group_key
;
GROUP_KEY P1_SUM P1_MAX P2_SUM P2_MAX ---------- ---------- ---------- ---------- ---------- G0 8 5 26 20 G1 9 9 5 5 G2 (null)
The query above also demonstrates that when both a pivot key alias (e.g. "as p1") and an aggregate alias (e.g. "AS SUM") are specified the resulting column names take the following form.
(pivot key alias)_(aggregate function alias).