Columns to Rows

Collection Method

In Oracle 10g or earlier another technique for transforming columns into rows uses a VARRAY collection data type and optionally an SQL Object Type, both introduced in Oracle 8i. (For Oracle 11g see UNPIVOT (11g).)

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
 

Method 1

Method 1 uses two user defined data types which were created in the Setup topic for this section.

desc name_value_pair
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 NAME                                   VARCHAR2(30)
 VALUE                                  VARCHAR2(5)


desc name_value_varray
 name_value_varray VARRAY(10) OF NAME_VALUE_PAIR
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 NAME                                   VARCHAR2(30)
 VALUE                                  VARCHAR2(5)

 

The solution looks like this.

break on key skip 1 duplicates

select
  key ,
  VALS.NAME  as source ,
  VALS.VALUE as val
from
  t ,
  table
  ( NAME_VALUE_VARRAY
    ( NAME_VALUE_PAIR( 'C1', t.c1 ),
      NAME_VALUE_PAIR( 'C2', t.c2 ),
      NAME_VALUE_PAIR( 'C3', t.c3 )
    )
  ) vals
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
 

Method 2

If we do not require the SOURCE column in our output we can use a solution requiring only a single user defined data type.

desc varray_type
 varray_type VARRAY(10) OF VARCHAR2(5)

 
select key, VALS.COLUMN_VALUE as val
from   t, table( VARRAY_TYPE( t.c1, t.c2, t.c3 ) ) vals
order by key ;
 
KEY VAL
--- -----
r1  v2
r1  v1
r1  v3

r2  v1
r2  v2
r2

r3  v1
r3
r3  v3

r4
r4  v1
r4

r5  v2
r5
r5  v3

r6  v2
r6
r6

r7
r7
r7  v3

r8
r8
r8
 

Gotchas

Some might be tempted to use a solution like the following, which attempts to use the ROW_NUMBER() function to select VARRAY element index numbers (e.g. 1, 2, 3). As the results below show, we cannot rely on the ROW_NUMBER() values to match their respective VARRAY index numbers.

select
  key ,
  'C' || ROW_NUMBER () OVER (PARTITION BY KEY ORDER BY 1) as source ,
  vals.column_value as val
from
  t ,
  table( varray_type( t.c1, t.c2, t.c3 ) ) vals
;
 
KEY SOURCE VAL
--- ------ -----
r1  C1     v2
r1  C2     v1
r1  C3     v3

r2  C1     v1
r2  C2     v2
r2  C3

r3  C1     v1
r3  C2
r3  C3     v3

r4  C1
r4  C2     v1
r4  C3

r5  C1     v2
r5  C2
r5  C3     v3

r6  C1     v2
r6  C2
r6  C3

r7  C1
r7  C2
r7  C3     v3

r8  C1
r8  C2
r8  C3

 

In the results above the SOURCE value for (KEY,VAL) = (r1,v2) should really be C2, for (r1,v1) it should be C1, for (r4,v1) it should be C1, etc.




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

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

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