Single SQL SELECT Returning multiple rows from one table row

sql oracle select plsql oracle9i
James L picture James L · May 20, 2009 · Viewed 31.5k times · Source

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

Adam Robinson picture Adam Robinson · May 20, 2009

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.

Related questions

Proper way of checking if row exists in table in PL/SQL block

I was writing some tasks yesterday and it struck me that I don't really know THE PROPER and ACCEPTED way of checking if row exists in table when I'm using PL/SQL. For examples sake let's use table: PERSON (ID, …

Read More
DATEDIFF function in Oracle

I need to use Oracle but DATEDIFF function doesn't work in Oracle DB. How to write the following code in Oracle? I saw some examples using INTERVAL or TRUNC. SELECT DATEDIFF ('2000-01-01','2000-01-02') AS …

Read More
If statement in select (ORACLE)

Hi I have simply select and works great: select 'CARAT Issue Open' issue_comment, i.issue_id, i.issue_status, i.issue_title, i.ISSUE_summary ,i.issue_description, i.severity, gcrs.Area_name, gcrs.sector_name, substr(gcrs.stream_…

Read More