Single SQL SELECT Returning multiple rows from one table row

Question

We have a table which is of the form:

ID,Value1,Value2,Value3
1,2,3,4

We need to transform this into.

ID,Name,Value
1,'Value1',2
1,'Value2',3
1,'Value3',4

Is there a clever way of doing this in one SELECT statement (i.e without UNIONs)? The column names Value1,Value2 and Value3 are fixed and constant.

The database is oracle 9i.

Answer 1

Give a union a shot.

select ID, 'Value1' as Name, Value1 as Value from table_name union all
select ID, 'Value2', Value2 as Value from table_name union all
select ID, 'Value3', Value3 as Value from table_name

order by ID, Name

using union all means that the server won't perform a distinct (which is implicit in union operations). It shouldn't make any difference with the data (since your ID's should HOPEFULLY be different), but it might speed it up a bit.

Answer 2

This works on Oracle 10g:

select id, 'Value' || n as name,
       case n when 1 then value1 when 2 then value2 when 3 then value3 end as value
from (select rownum n
      from (select 1 from dual connect by level <= 3)) ofs, t

I think Oracle 9i had recursive queries? Anyway, I'm pretty sure it has CASE support, so even if it doesn't have recursive queries, you can just do "(select 1 from dual union all select 2 from dual union all select 3 from dual) ofs" instead. Abusing recursive queries is a bit more general- for Oracle. (Using unions to generate rows is portable to other DBs, though)