CODEWITHSUNDEEP
sqlsql-server
Ryan Abarquez
Ryan Abarquez

Reputation: 307

SQL: How to select and add the second column below the first column to create a one column

I have a table: tblperson

There are two columns in tblperson:

person1         person2
Anna            Sarah
Louis           Mike
Kisha

I want to add the second column (person2) below the first column (person1) to create only one column (persons)

persons
Anna
Louis
Kisha
Sarah
Mike

I've tried using UNION but it would make my query large especially when there are more than two columns involved

Can anyone give hints to query for generating these records?

Thank you

Upvotes: 0

Views: 906

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You would do this using union all, but also with order by:

select person
from (select person1 as person, 1 as which from tblperson union all
      select person2 as person, 2 from tblperson
     ) p
order by which;

The order by is pretty important. It is true that you can simply do:

select person1 as person from tblperson union all
select person2 as person from tblperson

However, I consider this a little dangerous, because SQL standards do not guarantee that the first subquery is executed before the second. In practice, SQL Server does execute these in order, but there is no ongoing guarantee.

Upvotes: 3

Related Questions