Rows to Columns

Transposing Many to One With Unknown Pivot Keys

This topic demonstrates how to collect and spread values from multiple rows under a single column, like those in VAL below

GROUP_KEY  PIVOT_KEY  VAL
---------- ---------- ----------
G0         P0         a
G0         P0         b
G0         P0         (null)

G0         P1         A
G0         P1         BB
G0         P1         CCC

G0         P2         1
G0         P2         2222

G0         (null)     xyz
G0         (null)     (null)

G1         P0         a
G1         P0         b
G1         P0         c

G1         P2         1
G1         P2         2

(null)     P0         c

(null)     P1         C

(null)     (null)     (null)
 

across an unknown number of columns, rows, and groups like this.

GROUP_KEY P0 P1  P2   UNASSIGNED
--------- -- --- ---- ----------
G0        a  A   1    xyz
G0        b  BB  2222
G0           CCC

G1        a      1
G1        b      2
G1        c

(null)    c  C
 

Unlike the Transposing M:1 topic, here we assume we do not know, in advance, what the PIVOT_KEY values will be or how many distinct PIVOT_KEY values will be encountered in the base table. However, we will assume we know the maximum possible number of distinct PIVOT_KEY values (we will use 5 in our examples).

Solution

In the following code we will see a static query that appears to be dynamic. I.e. it appears to return a variable number of columns with variable column headings depending on the data selected. The effect is achieved by using certain SQL*Plus features to hide unused columns. As an added bonus the technique allows us to dynamically size each column to fit the largest value that appears under it.

Now let's look at the first part of the solution which does some setup work. Its output is not normally displayed.

set feedback off

column c1_print    new_value v1_print    noprint
column c2_print    new_value v2_print    noprint
column c3_print    new_value v3_print    noprint
column c4_print    new_value v4_print    noprint
column c5_print    new_value v5_print    noprint

column c1_heading  new_value v1_heading  noprint
column c2_heading  new_value v2_heading  noprint
column c3_heading  new_value v3_heading  noprint
column c4_heading  new_value v4_heading  noprint
column c5_heading  new_value v5_heading  noprint

column c1_format   new_value v1_format   noprint
column c2_format   new_value v2_format   noprint
column c3_format   new_value v3_format   noprint
column c4_format   new_value v4_format   noprint
column c5_format   new_value v5_format   noprint

select
  max( decode( c_num, 1, c_print, 'noprint' ) ) as c1_print ,
  max( decode( c_num, 2, c_print, 'noprint' ) ) as c2_print ,
  max( decode( c_num, 3, c_print, 'noprint' ) ) as c3_print ,
  max( decode( c_num, 4, c_print, 'noprint' ) ) as c4_print ,
  max( decode( c_num, 5, c_print, 'noprint' ) ) as c5_print
  ,
  max( decode( c_num, 1, c_heading, null ) ) as c1_heading ,
  max( decode( c_num, 2, c_heading, null ) ) as c2_heading ,
  max( decode( c_num, 3, c_heading, null ) ) as c3_heading ,
  max( decode( c_num, 4, c_heading, null ) ) as c4_heading ,
  max( decode( c_num, 5, c_heading, null ) ) as c5_heading
  ,
  max( decode( c_num, 1, c_format, null ) ) as c1_format ,
  max( decode( c_num, 2, c_format, null ) ) as c2_format ,
  max( decode( c_num, 3, c_format, null ) ) as c3_format ,
  max( decode( c_num, 4, c_format, null ) ) as c4_format ,
  max( decode( c_num, 5, c_format, null ) ) as c5_format
from
  (
    select
      row_number() over ( order by pivot_key ) as c_num ,
      'print'                                  as c_print ,
      nvl(pivot_key, 'UNASSIGNED' )            as c_heading ,
      'a' ||
        greatest
        ( max( nvl(length(nvl(pivot_key, 'UNASSIGNED' )),0) ),
          max( nvl(length(val),0) )
        )
        as c_format
    from t3
    where regexp_like( val, '[abcABC]' ) -- Note 1
    group by pivot_key
  )
;



column c1 &v1_print heading &v1_heading format &v1_format
column c2 &v2_print heading &v2_heading format &v2_format
column c3 &v3_print heading &v3_heading format &v3_format
column c4 &v4_print heading &v4_heading format &v4_format
column c5 &v5_print heading &v5_heading format &v5_format
 

Note 1: This line is not part of the solution. It simply simulates a typical WHERE clause.

The next step displays the final result. Columns for PIVOT_KEY's that are not in the result set will not be displayed.

column group_key null '(null)'

select
  group_key ,
  max( decode( pivot_num, 1, val, null ) ) as C1 ,
  max( decode( pivot_num, 2, val, null ) ) as C2 ,
  max( decode( pivot_num, 3, val, null ) ) as C3 ,
  max( decode( pivot_num, 4, val, null ) ) as C4 ,
  max( decode( pivot_num, 5, val, null ) ) as C5
from
  ( select
      group_key ,
      row_number()
        over( partition by group_key, pivot_key order by val )
        as row_num ,
      dense_rank() over ( order by pivot_key ) as pivot_num ,
      val
    from t3
    where regexp_like( val, '[abcABC]' )
  )
group by group_key, row_num
order by group_key, row_num
;
 
GROUP_KEY  P0 P1
---------- -- ---
G0         a  A
G0         b  BB
G0            CCC
G1         a
G1         b
G1         c
(null)     c  C
 

The last query returned two PIVOT_KEY columns. Let's see the same query return four columns (we remove the WHERE clause discussed in Note 1 above).

set feedback off

select
  max( decode( c_num, 1, c_print, 'noprint' ) ) as c1_print ,
  max( decode( c_num, 2, c_print, 'noprint' ) ) as c2_print ,
  max( decode( c_num, 3, c_print, 'noprint' ) ) as c3_print ,
  max( decode( c_num, 4, c_print, 'noprint' ) ) as c4_print ,
  max( decode( c_num, 5, c_print, 'noprint' ) ) as c5_print
  ,
  max( decode( c_num, 1, c_heading, null ) ) as c1_heading ,
  max( decode( c_num, 2, c_heading, null ) ) as c2_heading ,
  max( decode( c_num, 3, c_heading, null ) ) as c3_heading ,
  max( decode( c_num, 4, c_heading, null ) ) as c4_heading ,
  max( decode( c_num, 5, c_heading, null ) ) as c5_heading
  ,
  max( decode( c_num, 1, c_format, null ) ) as c1_format ,
  max( decode( c_num, 2, c_format, null ) ) as c2_format ,
  max( decode( c_num, 3, c_format, null ) ) as c3_format ,
  max( decode( c_num, 4, c_format, null ) ) as c4_format ,
  max( decode( c_num, 5, c_format, null ) ) as c5_format
from
  (
    select
      row_number() over ( order by pivot_key ) as c_num ,
      'print'                                  as c_print ,
      nvl(pivot_key, 'UNASSIGNED' )               as c_heading ,
      'a' ||
        greatest
        ( max( nvl(length(nvl(pivot_key, 'UNASSIGNED' )),0) ),
          max( nvl(length(val),0) )
        )
        as c_format
    from t3
    group by pivot_key
  )
;



column c1 &v1_print heading &v1_heading format &v1_format
column c2 &v2_print heading &v2_heading format &v2_format
column c3 &v3_print heading &v3_heading format &v3_format
column c4 &v4_print heading &v4_heading format &v4_format
column c5 &v5_print heading &v5_heading format &v5_format
 
select
  group_key ,
  max( decode( pivot_num, 1, val, null ) ) as C1 ,
  max( decode( pivot_num, 2, val, null ) ) as C2 ,
  max( decode( pivot_num, 3, val, null ) ) as C3 ,
  max( decode( pivot_num, 4, val, null ) ) as C4 ,
  max( decode( pivot_num, 5, val, null ) ) as C5
from
  ( select
      group_key ,
      row_number()
        over( partition by group_key, pivot_key order by val )
        as row_num ,
      dense_rank() over ( order by pivot_key ) as pivot_num ,
      val
    from t3
  )
group by group_key, row_num
order by group_key, row_num
;
 
GROUP_KEY  P0 P1  P2   UNASSIGNED
---------- -- --- ---- ----------
G0         a  A   1    xyz
G0         b  BB  2222
G0            CCC
G1         a      1
G1         b      2
G1         c
(null)     c  C
 

Note how the widths of the P0 and UNASSIGNED columns fit the column heading whereas the widths of the P1 and P2 columns fit the largest data value.

Other Solutions

While the solution above has the advantage of being a pure SQL solution (no custom PL/SQL modules are required) it does rely on certain features available only with SQL*Plus. Solutions which do not rely on SQL*Plus can be found at the following links.

Tags

Dynamic Columns
Dynamic Number of Columns
Dynamic Column Headings
Dynamic Column Width or Format
Unknown Columns
Unknown Number of Columns



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-12200.html]SQL Snippets: Rows to Columns - Transposing Many to One With Unknown Pivot Keys[/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-12200.html">SQL Snippets: Rows to Columns - Transposing Many to One With Unknown Pivot Keys</a>

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

  • Link Text : SQL Snippets: Rows to Columns - Transposing Many to One With Unknown Pivot Keys
  • URL (href): http://www.sqlsnippets.com/en/topic-12200.html

Revision Notes

Date Category Note
2009-06-23 Revision Removed unnecessary TTITLE command from solution.