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.

Pepperandsalt

What DB server are you using? – Waldenses

Are 1,2,3,4 columns or data values? – Darksome

This is pretty similar to this question – Britnibrito

The cleverest way that I can think of is to redesign your database so that it's normalized :) – Derris

Answer 1

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)

Anthropo

+1 for the single table scan. WRT recursive queries - Oracle has had hierarchical queries since 7, but only introduced the recursive WITH clause in 11gR2. – Barraza

Answer 2

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.

Pesach

Nice, that's the way I would do it- I like the UNION ALL optimization. Minor error-in the example there is no value4. I guess an alternative way is to unpivot, depending if the DB supports this functionality. – Vigil

Using 'union all' definitely won't make any difference to the output since 'Value1', 'Value2' and 'Value3' are all distinct! But it will avoid the DB uselessly trying to uniquify the rows. – Anthropo

@James: The question was edited to add that caveat after my answer was posted. – Pesach

The question specified a single SELECT from the beginning. Just sayin'. – Pepperandsalt

@James: It's a single statement in the sense that it produces one result set. – Pesach

OK, I think we may both be splitting hairs now ;) The only reason I commented as I noticed this is far and away my most popular question. – Pepperandsalt

Problem is this will involve three scans over the table, whereas the best option is to scan only once. – Barraza

Answer 3

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

Titlark

Answer 4

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

Dormant

Answer 5

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

Dehiscent

Answer 6

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.

Derris

Answer 7

CTE syntax may be different for Oracle (I ran it in Teradata), but I only used CTE to provide test data, those 1 2 3 and 4. You can use temp table instead. The actual select statement is plain vanilla SQL and it will on any relational database.

Flagging

Answer 8

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.

Cleodell

Answer 9

Try this:

CTE creates a temp table with 4 values. You can run this as is in any database.

with TEST_CTE (ID) as

(select * from (select '1' as a) as aa  union all
select * from (select '2' as b) as bb  union all
select * from (select '3' as c) as cc  union all
select * from (select '4' as d) as dd )

select a.ID, 'Value'|| a.ID, b.ID
from TEST_CTE a, TEST_CTE b
where b.ID = (select min(c.ID) from TEST_CTE c where c.ID > a.ID)

Here is the result set:

1   Value1  2

2   Value2  3

3   Value3  4

Enjoy!

Some afterthoughts.

^^^ CTE syntax may be different in Oracle. I could only run it in Teradata. You can substitute it with temp table or fix the syntax to make it Oracle compatible. The select statement is plain vanilla SQL that will work on any database.

^^^ Another thing to note. If ID field is numeric, you might need to cast it into CHAR in order to concatenate it with "Value".

Flagging

I just added some comments to my post. – Flagging