CODEWITHSUNDEEP
sqloracle-database
Sunny88
Sunny88

Reputation: 2940

Unroll table with multiple keys and values in each row with sql

How can I "unroll" a table with multiple keys and values in each row to a table with one key and value in each row?

For example I have a table 

key1 value1 key2 value2  
a    1      b    2  
c    3      d    4  
e    5      f    6

and I want to change it into 

key value  
a   1  
b   2  
c   3  
d   4  
e   5  
f   6

There are only two keys and values in each row above but I need the solution for general case, where I may have up to 7 pairs in each row.

Upvotes: 0

Views: 420

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

The most efficient way to do this is using a cross join:

select (case when n.n = 1 then key1 else key2 end) as "key",
       (case when n.n = 1 then value1 else value2 end) as "value"
from table t cross join
     (select 1 as n from dual union all select 2 from dual 
     ) n;

The reason this is more efficient than the union all approach is because the table is scanned only once. In many cases, this performance increment is not important (one table scan versus two), but in some cases it can be important.

Upvotes: 3

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

just use union / union all ?

Assuming key1 and key2 are of same type, as value1 and value2

select key1, value1
from table
union all -- or just union to merge duplicates
select key2, value2
from table

Upvotes: 1

Related Questions