sql combining output of 2 columns into one column

I've following tables

person: id,name
1, mani
2, senthil
3, jack
4, alan

relation: relname,id1,id2 (id1 and id2 refers to person.id)
rel1, 1,3
rel2, 2,3
rel1, 4,1

I want to form a query that retur开发者_如何学运维ns the ids of person that are related one after another. Query should return:

1
3
2
3
4
1

Here (1,3) is pair, followed by (2,3) and so on.

Can you please form such a query?

One Way (assumes id1,id2 is a composite PK to avoid duplicates)

SELECT id
FROM
(
select id1 as id, id1,id2 from relation
union all
select id2 as id, id1,id2 from relation
) T
ORDER BY id1,id2

If your RDBMS supports UNPIVOT you may be able to do this with one scan through the table.

select id1 from relation
union
select id2 from relation

(but the order won't be the one in your example).

You could also iterate through the pairs and build a list :

// metalanguage
resultSet = executeQuery("select id1, id2 from relation");
List list = new List();
while (resultSet.hasNext()) {
    list.add(resultSet.get("id1");
    list.add(resultSet.get("id2");
}

You could first try to create column result like CSV

 1,3,2,3,4,1

And afterwards from this CVS retrieve rows.

The creation of this query depend of DBMS you use.

I think that using some internet search engine (amazon,bing, google) we will be able to solve this by your own.