I have a table with the following columns:

col1 col2
1     1
1     2
1     1
2     1
3     3
3     2
3     1
3     2
3     4
3     1

I want to get all the combinations for each value of col2 with everything else but itself group by col1, i.e. achieve the following output:

col1 col2 col2_combo
1    1    2
3    3    2
3    3    1
3    3    4
3    2    1
3    2    4
3    1    4

I have tried using self join of the table but I can't get a unique result, i.e. a single line for every two values combined.

CodePudding user response：

There are duplicates in the table. So, the first step is to select distinct values. Well, and then just join the table to itself.

with pairs as (select distinct col1, col2 from mytable)
select a.col1, a.col2, b.col2
from pairs a
join pairs b on b.col1 = a.col1 and b.col2 > a.col2
order by a.col1, a.col2, b.col2;