Multiple Combinations Of One Column Based On Another

I have a table like this (all my id2s are different) : id1 id2 --- --- A 1 A 2 B 3 C 4 and I want to generate a list of combinations of id2 for eac

Solution 1:

Well I'm proud of me (LOL) I found something not bad. ;-)

'compte' is a SELECT COUNT(DISTINCT id1) FROM my_table

'combis' are all my combinations (eg. [(A, 1), (A, 2), (B, 3), (B, 4), (C, 5), (D, 6)]

# i generate my own list comprehension

for k in range(compte):
    toto = 'a'+str(k)+'[1]' + ( ',' if k != 0 else '' ) + toto
    titi = 'for a'+str(k)+' in combis ' + titi
    tutu = 'a'+str(k)+'[0]' + ( '>' if k != 0 else '' ) + tutu

exec('print([['+toto+'] '+titi+'if '+tutu+'])')

Solution 2:

I suppose that the id-data comes in the following form:

combis = [('A', 1), ('A', 2), ('B', 3), ('B', 4), ('C', 5), ('D', 6)]

Then

from collections import defaultdict
from itertools import product

groups = defaultdict(list)
for id1, id2 in combis:
    groups[id1].append(id2)
result = list(product(*groups.values()))

produces

[(1, 3, 5, 6), (1, 4, 5, 6), (2, 3, 5, 6), (2, 4, 5, 6)]

Solution 3:

with combinationTable as (
 select DENSE_RANK() over (Order By id1) as rn ,id2, id1 from test
)
select c1.id2,c2.id2,c3.id2
from combinationTable c1, combinationTable c2, combinationTable c3
where
 c1.rn < c2.rn and
 c2.rn < c3.rn
order by c1.rn,c2.rn,c3.rn

Of course the "From clause" and "Where clause" will be modified if you want to choose 4 id2 instead of 3