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.