How to select pairs of columns with SQL

I can't fit the question into one sentence so I will give an example. S开发者_开发技巧ay you have the following tables in your database:

Users(id, name)
Friends(id1, id2)

Friends(id1) and Friends(id2) are both foreign keys that reference Users(id). What would the query look like to get the user id on one side and all friends on the other side. The data in the friends table looks something like this:

id1 id2
-------
 1  2
 1  3
 1  4
 2  3
 2  4
 2  5
 5  1

And I want an output like this:

id1 id2
-------
 1  2
 1  3
 1  4
 1  5
 2  1
 2  3
 2  4
 2  5
 3  1
 3  2
 4  1
 4  2
 5  1
 5  2

All user IDs should be on the left and the friends of that user ID should all be on the right. I think I have to do some sort of cross product but I can't get the correct output. Any ideas how to do this? Thanks.

union together the combinations of id1,id2 with id2,id1:

;with FriendShips as (
    select
        id1, id2
    from
        Friends F1

    union

    select
        id2, id1
    from
        Friends F2
)
select
    u1.name, u2.name
from
    FriendShips F
inner join
    Users u1 on F.id1 = u1.id
inner join
    Users u2 on F.id2 = u2.id

This should do it

select id1, id2
from friends
union
select id2, id1
from friends

Beat by 38 secs... I'll throw an explanation then. If a is a friend of b, and it is stored as (a,b), you select the columns in reverse order (b,a), so that you end up with both variations in the union result.

Union = performs a distinct
Union All = keeps duplicate results

Using Union allows you to sometimes store (a,b) as well as (b,a) and this query will still show the combination only twice, in both a-b and b-a orientations.