CODEWITHSUNDEEP
sqlsqlite
Manish
Manish

Reputation: 2105

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 returns 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?

Upvotes: 1

Views: 4098

Answers (3)

Damian Leszczyński - Vash
Damian Leszczyński - Vash

Reputation: 30865

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.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453287

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.

Upvotes: 5

JB Nizet
JB Nizet

Reputation: 691755

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");
}

Upvotes: 0

Related Questions