Flexible Row Generator with Oracle 11g Unpivot Operator

Row generation is a very useful technique for many (semi-)advanced SQL Queries. We have discussed various methods for row-generation in previous articles on our weblog. Examples are the CUBE operator, Table Functions and the Connect By Level < #number of records approach, apart from good old UNION ALL with multiple select from dual. These approaches vary in flexibility and compactness. CUBE and Connect By allow for easy generation of a large number of rows with little or complex control over the values in those rows, while the UNION ALL is elaborate and bulky, even though it gives great control over the exact values.

The Oracle 11g Unpivot operator presents us with a new way of generating rows with great control over the values in those rows and a more compact and elegant syntax than the UNION ALL alternative.

Let’s look at a simple example.
Suppose we need a set of rows with specific values, probably to use as inline view inside our complex query or as a stand-alone view. In this example I have taken six rather useless values, but it’s the concept that matters.

In a single select-statement against DUAL – rather than six queries on DUAL that are UNION ALLed together – we select the six required values as individual columns – a through f. The UNPIVOT we then have act on this result takes the single row with six columns and turns it into six rows with two columns, one to hold the name of the source column the row is created from and one for the value in that source column:

select *
from   ( ( select 'value1'  a
           ,      'value27' b
           ,      'value534' c
           ,      'value912' d
           ,      'value1005' e
           ,      'value2165' f
           from dual       
         )
              unpivot 
              ( value 
                for value_type in ( a,b,c,d,e, f)
              )
       )
/

The result of this query:

V VALUE
- ---------
A value1
B value27
C value534
D value912
E value1005
F value2165

6 rows selected.

Note: in situations where straightforward, bulk-generation of rows is required, using the CONNECT BY approach will still be superior. For example to generate the alphabet, use a statement such as:

 1  select chr(rownum+64) letter
 2  from   (select level
 3          from   dual
 4          connect
 5          by     level<27
 6*        )

However, to generate a subset, say all vowels, the UNPIVOT approach may prove useful.

select vowel
from ( ( select ‘a’ v1
, ‘e’ v2
, ‘i’ v3
, ‘o’ v4
, ‘u’ v5
from dual
)
unpivot
( vowel
for dummy in ( v1,v2,v3,v4,v5)
)
)
/