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

You can do it like this, but it's not pretty:

SELECT id,'Value 1' AS name,value1 AS value FROM mytable
UNION
SELECT id,'Value 2' AS name,value2 AS value FROM mytable
UNION
SELECT id,'Value 3' AS name,value3 AS value FROM mytable

Answer 3

Unioning three select statements should do the trick:

SELECT ID, 'Value1', Value1 AS Value
FROM TABLE
UNION
SELECT ID, 'Value2', Value2 AS Value
FROM TABLE
UNION
SELECT ID, 'Value3', Value3 AS Value
FROM TABLE

Answer 4

If you're using SQL Server 2005+ then you can use UNPIVOT

CREATE TABLE #tmp ( ID int, Value1 int, Value2 int, Value3 int)

INSERT INTO #tmp (ID, Value1, Value2, Value3) VALUES (1, 2, 3, 4)

SELECT
 *
FROM
 #tmp

SELECT
 *
FROM
 #tmp
UNPIVOT
(
 [Value] FOR [Name] IN (Value1, Value2, Value3)
) uPIVOT

DROP TABLE #tmp

Answer 5

This is pretty similar to this question

Answer 6

For Sql Server, consider UNPIVOT as an alternative to UNION:

SELECT id, value, colname
FROM #temp t
UNPIVOT (Value FOR ColName IN (value1,value2,value3)) as X

This will return the column name as well. I'm unsure what the X is used for, but you can't leave it out.

Answer 7

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)

Answer 8

A UNION ALL, as others have suggested, is probably your best bet in SQL. You might also want to consider handling this in the front end depending on what your specific requirements are.

Answer 9

1. Do you really need to do this in the database? Wouldn't a client program--report writer, script, whatever, be a better place to accomplish it?
2. If you must, have a look at the documentation on pipelined functions. They will likely be pretty efficient.