subquery select with multiple row value

Question

so i have this query

select oldvalue, newvalue from(select 'a' as oldvalue, 'b' as newvalue) as N

now the problem is i can only have 1 value each column when i want to have multiple value in column, i have tried using union all like this

select oldvalue, newvalue from(select 'a' as oldvalue, 'b' as newvalue UNION ALL 'c', 'd') as N

with this now i have a and c as oldvalue and b and d as newvalue, but with union all i can only have 2 max of value in each column, is there a way for me to have more than two value in each column

The number of columns which can appear in a select is not limited to 2. Can you better explain what you are trying to do? — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 27, 2019 at 4:17
i want to make temporary table with 2 column, and each column can have multiple value, i want to add a value in a column not add another column — Misael Indra Wijaya
– Misael Indra Wijaya, Commented Dec 27, 2019 at 4:18
@TimBiegeleisen oh okay, one last question, can i have multiple union all select? like more than one union? — Misael Indra Wijaya
– Misael Indra Wijaya, Commented Dec 27, 2019 at 4:22
Yes, you can do that. Note that you don't need to repeat the alias you define in the very select in the union. Subsequent aliases will in fact just be ignored anyway, only the first one matters. — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 27, 2019 at 4:23

Suresh Gajera · Accepted Answer · 2019-12-27 05:25:57Z

1

Just writing in CODE what @TimBiegeleisen has mention in the comment.

  SELECT oldvalue, newvalue 
  FROM ( SELECT 'a' as oldvalue, 'b' as newvalue UNION ALL 
       SELECT 'c', 'd' UNION ALL
       SELECT 'e' ,'f' UNION ALL
       SELECT 'g' ,'h' UNION ALL
       SELECT 'i','j' ......
      ) as N

answered Dec 27, 2019 at 5:25

Suresh Gajera

3621 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

subquery select with multiple row value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related