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
In Oracle 10g and earlier versions the PIVOT command used in the previous topic is not available. Fortunately we can easily achieve the desired results in any version of Oracle using the DECODE function instead.
column group_key null '(null)' select group_key , sum( decode( pivot_key, 'P0', val, null ) ) as p0_sum , sum( decode( pivot_key, 'P1', val, null ) ) as p1_sum , sum( decode( pivot_key, 'P2', val, null ) ) as p2_sum , sum( decode( pivot_key, null, val, null ) ) as unassigned_sum from t2 group by group_key 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
Aggregate functions other than SUM can be used to provide a different analysis. The following examples display the average and maximum VAL's for each week/day combination.
select group_key , AVG( decode( pivot_key, 'P0', val, null ) ) as AVG_P0 , AVG( decode( pivot_key, 'P1', val, null ) ) as AVG_P1 , AVG( decode( pivot_key, 'P2', val, null ) ) as AVG_P2 , AVG( decode( pivot_key, null, val, null ) ) as AVG_UNASSIGNED from t2 group by group_key order by group_key ;
GROUP_KEY AVG_P0 AVG_P1 AVG_P2 AVG_UNASSIGNED ---------- ---------- ---------- ---------- -------------- G0 10 4 13 G1 9 5 G2 7 (null) 15
select group_key , MAX( decode( pivot_key, 'P0', val, null ) ) as MAX_P0 , MAX( decode( pivot_key, 'P1', val, null ) ) as MAX_P1 , MAX( decode( pivot_key, 'P2', val, null ) ) as MAX_P2 , MAX( decode( pivot_key, null, val, null ) ) as MAX_UNASSIGNED from t2 group by group_key order by group_key ;
GROUP_KEY MAX_P0 MAX_P1 MAX_P2 MAX_UNASSIGNED ---------- ---------- ---------- ---------- -------------- G0 10 5 20 G1 9 5 G2 10 (null) 15
How it Works
This solution forms the basis for the remaining topics in this section. It is therefore important to get a solid grasp on how it works before continuing to the next topic. To do this we will consider the following queries which illustrate conceptual steps in building a MAX value solution.
First let's look at a simple query of base table columns.
break on group_key skip 1 duplicates select group_key, pivot_key, val from t2 order by group_key, pivot_key ;
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 G1 P2 G1 P2 5 G2 4 G2 7 G2 10 (null) P0 15 (null) P2 (null)
Next we distribute VAL values across separate columns using the DECODE function.
select group_key , pivot_key , DECODE( pivot_key, 'P0', val, null ) as VAL_P0 , DECODE( pivot_key, 'P1', val, null ) as VAL_P1 , DECODE( pivot_key, 'P2', val, null ) as VAL_P2 , DECODE( pivot_key, null, val, null ) as VAL_UNASSIGNED from t2 order by group_key, pivot_key ;
GROUP_KEY PIVOT_KEY VAL_P0 VAL_P1 VAL_P2 VAL_UNASSIGNED ---------- ---------- ---------- ---------- ---------- -------------- G0 P0 10 G0 P1 5 G0 P1 3 G0 P2 20 G0 P2 6 G1 P1 9 G1 P1 G1 P2 G1 P2 5 G2 4 G2 7 G2 10 (null) P0 15 (null) P2 (null)
Finally we can collapse the values under the VAL_P0, VAL_P1, VAL_P2, and VAL_UNASSIGNED columns using a GROUP BY clause plus an aggregate function like MAX.
clear breaks select group_key , MAX( decode( pivot_key, 'P0', val, null ) ) as MAX_P0 , MAX( decode( pivot_key, 'P1', val, null ) ) as MAX_P1 , MAX( decode( pivot_key, 'P2', val, null ) ) as MAX_P2 , MAX( decode( pivot_key, null, val, null ) ) as MAX_UNASSIGNED from t2 GROUP BY GROUP_KEY order by group_key ;
GROUP_KEY MAX_P0 MAX_P1 MAX_P2 MAX_UNASSIGNED ---------- ---------- ---------- ---------- -------------- G0 10 5 20 G1 9 5 G2 10 (null) 15
Trivia
I first came up with the idea of using DECODE and MAX to distribute and collapse values in response to a support question from a client while working in Oracle Canada's technical support department back in 1989 (see Joe's Blog: The Journey Begins). After publishing the solution in a newsletter and a support bulletin I pretty much forgot about it, except when replying to the occasional pivot question on internal mailing lists.
Imagine my surprise when, while browsing SQL topics on the Internet a few years back, I found this approach had become the de facto solution for pivot queries in Oracle versions prior to 11g (over 50,000 hits on Google). Curiously enough, after 18 years of SQL programming I can't say I have ever found the need to use the technique in any of my own code. :-)