Rows to Columns

Aggregating Values (11g)

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




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-12758.html]SQL Snippets: Rows to Columns - Aggregating Values (11g)[/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-12758.html">SQL Snippets: Rows to Columns - Aggregating Values (11g)</a>

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

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