yjc yjc - 1 month ago 6x
SQL Question

SQL - Return all unique pairs of values from one column, for each value in another column

Suppose the table looks like

col1 col2
---- ----
1 a
2 a
3 a
4 b
5 b
6 b

and I want to get all pairs from col1 for each value in col2, meaning the result should look like:

col1a col1b col2
---- ----- ----
1 2 a
1 3 a
2 3 a
4 5 b
4 6 b
5 6 b

I have tried to use

temp1 cross join temp2 where temp1.col1 < temp2.col1 order by temp1.col1, temp2.col1

as part of the full query, but it's not returning all the possible combinations. Also I'm not sure how I should write the "for each" part of the command, as in "for each value in col2, create all pairs from the value in col1". Any guidance will be much appreciated.

jpw jpw

Maybe this is what you are looking for?

select t1.col1 as col1a, t2.col1 as col1b, t1.col2 
from t as t1
join t as t2 on t1.col2 = t2.col2
where t1.col1 < t2.col1

A sample SQL Fiddle gives the same output as your example.