0

I have following table,

 _________________ |id key1 key2 key3| ------------------ 1 101 102 103 2 201 202 203

I need a query which will create the following output,

 |id key| -------- 1 101 1 102 1 103 2 201 2 202 2 203

Is there anything other than union all? When I used "union all", i came across an error disk utilization full... I have billions of records.

Improve this question
3
  • Why would you want alternative for the most natural thing to do here? Commented Jun 21, 2016 at 13:13
  • As to "Is there anything other than union all" - yes. You should normalize this table to avoid the repeated 'key' columns. This is in fact a textbook example of why tables should be normalized. Commented Jun 21, 2016 at 13:19
  • Hang on... Which database are you using? You have tagged Oracle, PostgreSQL and Redshift. Commented Jun 22, 2016 at 0:06

3 Answers 3

Reset to default
2

Since the question is tagged Oracle, you could do:

SELECT id, key FROM table_name UNPIVOT INCLUDE NULLS ( key FOR key_name IN ( key1, key2, key3 ) );
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

union all is very efficient on Redshift. Doubt there's anything much better.

create table new_table as select id, key1 as key from old_table union all select id, key2 as key from old_table union all select id, key3 as key from old_table

If you want to try something like Mottor suggests you can replace the Oracle specific dual connect by level bit with a Redshift hack like so:

(select row_number() over (order by true) as l from stv_blocklist limit 3) b

The stv_blocklist table reference there could be any table with at least 3 rows.

Improve this answer

Comments

-1 
select a.id, case b.l when 1 then a.key1 when 2 then a.key2 else a.key3 end key from mytable a cross join (select level l from dual connect by level < 4) b order by 1,2
Improve this answer

4 Comments

Can you please explain the statement "select level l from dual connect by level < 4"
It is to generate numbers here is from 1 to 3. If you want to generate from 1 to 20 for sample, change 4 with 21
@Mottor...I would like to know the memory utilization of cross join....when I used "union all" instead of cross join I got disk full error...I have billions of records...which has more memory utilization union all or cross join?
You should test it. In Oracle there is Explain Plan which can show the predicted memory consumption. I don't know the Redshift. Union all makes separate queries. If you do not sort in memory, the cross join should be better

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.