Rows to Columns

Aggregating Values

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. :-)




Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-12188.html]SQL Snippets: Rows to Columns - Aggregating Values[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-12188.html">SQL Snippets: Rows to Columns - Aggregating Values</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Rows to Columns - Aggregating Values
  • URL (href): http://www.sqlsnippets.com/en/topic-12188.html