Columns to Rows

MODEL Method

In Oracle 10g another technique for transforming columns into rows uses the SELECT command's MODEL clause (not available prior to 10g). If you are unfamiliar with the MODEL clause review the SQL Features Tutorials: MODEL Clause tutorial before proceeding. (For Oracle 11g see UNPIVOT (11g). For versions prior to 10g see UNION ALL Method or Collection Method)

Consider this data table.

select key, c1, c2, c3 from t order by key ;
 
KEY C1    C2    C3
--- ----- ----- -----
r1  v1    v2    v3
r2  v1    v2
r3  v1          v3
r4  v1
r5        v2    v3
r6        v2
r7              v3
r8
 

We can convert each row of C1, C2, and C3 values into three rows of values under a single column as follows.

break on key duplicates skip 1

select key, source, val
from   t
model
  return updated rows
  partition by ( key )
  dimension by ( 0 as i )
  measures     ( 'xx' as source, 'xxxxx' as val, c1, c2, c3 )
  rules upsert all
  (
    source[ 1 ] = 'C1' ,
    source[ 2 ] = 'C2' ,
    source[ 3 ] = 'C3' ,
    val[ 1 ] = c1[ 0 ] ,
    val[ 2 ] = c2[ 0 ] ,
    val[ 3 ] = c3[ 0 ]
  )
order by key, source ;
 
KEY SOURCE VAL
--- ------ -----
r1  C1     v1
r1  C2     v2
r1  C3     v3

r2  C1     v1
r2  C2     v2
r2  C3

r3  C1     v1
r3  C2
r3  C3     v3

r4  C1     v1
r4  C2
r4  C3

r5  C1
r5  C2     v2
r5  C3     v3

r6  C1
r6  C2     v2
r6  C3

r7  C1
r7  C2
r7  C3     v3

r8  C1
r8  C2
r8  C3
 

Understanding How it Works

To understand how the solution works we will start with a simple MODEL query and build towards the final solution one step at a time.

clear breaks

column I format 99

select key, I, SOURCE, VAL, c1, c2, c3
from   t
where  key = 'r1'
model
  partition by ( key )
  dimension by ( 0 AS I )
  measures     ( 'xx' AS SOURCE, 'xxxxx' AS VAL, c1, c2, c3 )
  rules        ()
;
 
KEY   I SOURCE VAL   C1    C2    C3
--- --- ------ ----- ----- ----- -----
r1    0 xx     xxxxx v1    v2    v3
 
select key, i, source, val, c1, c2, c3
from   t
where  key = 'r1'
model
  partition by ( key )
  dimension by ( 0 as i )
  measures     ( 'xx' as source, 'xxxxx' as val, c1, c2, c3 )
  RULES UPSERT ALL
  (
    SOURCE[ 1 ] = 'C1' ,
    SOURCE[ 2 ] = 'C2' ,
    SOURCE[ 3 ] = 'C3'
  )
ORDER BY I ;
 
KEY   I SOURCE VAL   C1    C2    C3
--- --- ------ ----- ----- ----- -----
r1    0 xx     xxxxx v1    v2    v3
r1    1 C1
r1    2 C2
r1    3 C3
 
select key, i, source, val, c1, c2, c3
from   t
where  key = 'r1'
model
  partition by ( key )
  dimension by ( 0 as i )
  measures     ( 'xx' as source, 'xxxxx' as val, c1, c2, c3 )
  rules upsert all
  (
    source[ 1 ] = 'C1' ,
    source[ 2 ] = 'C2' ,
    source[ 3 ] = 'C3' ,
    VAL[ 1 ] = C1[ 0 ] ,
    VAL[ 2 ] = C2[ 0 ] ,
    VAL[ 3 ] = C3[ 0 ]
  )
order by i ;
 
KEY   I SOURCE VAL   C1    C2    C3
--- --- ------ ----- ----- ----- -----
r1    0 xx     xxxxx v1    v2    v3
r1    1 C1     v1
r1    2 C2     v2
r1    3 C3     v3
 
select key, i, source, val
from   t
where  key = 'r1'
model
  RETURN UPDATED ROWS
  partition by ( key )
  dimension by ( 0 as i )
  measures     ( 'xx' as source, 'xxxxx' as val, c1, c2, c3 )
  rules upsert all
  (
    source[ 1 ] = 'C1' ,
    source[ 2 ] = 'C2' ,
    source[ 3 ] = 'C3' ,
    val[ 1 ] = c1[ 0 ] ,
    val[ 2 ] = c2[ 0 ] ,
    val[ 3 ] = c3[ 0 ]
  )
order by i ;
 
KEY   I SOURCE VAL
--- --- ------ -----
r1    1 C1     v1
r1    2 C2     v2
r1    3 C3     v3
 



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-12034.html]SQL Snippets: Columns to Rows - MODEL Method[/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-12034.html">SQL Snippets: Columns to Rows - MODEL Method</a>

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

  • Link Text : SQL Snippets: Columns to Rows - MODEL Method
  • URL (href): http://www.sqlsnippets.com/en/topic-12034.html

Revision Notes

Date Category Note
2007-05-16 Revision Simplified the solution by adding PARTITION BY.