CODEWITHSUNDEEP
sqloracle-databaseplsqlcombinations
q4za4
q4za4

Reputation: 652

Oracle: how to get unique combinations of 2 sets

sample data:

create table group_ (
group_id number);

create table set_(
set_id number);

insert into group_ values(1);
insert into set_ values(1);
insert into set_ values(2);
insert into set_ values(3);

Data looks like group: 1 set: 1,2,3

I want to get all combinations of this 2 tables but UNIQUE ( 1,2,3 it's same as 1,3,2), so output would be like this:

    1
    1-1
    1-2
    1-3
    1-12
    1-13
    1-23
    1-123

I tried using connect_by_path, but it get's me or same combinations (like 1-132) or some combinations are missing. Any ideas?

Upvotes: 1

Views: 195

Answers (1)

Aleksej
Aleksej

Reputation: 22949

Assuming that you don't have duplicated values in set_ and that you missed 1-23 in the expected result, this could be a way:

select to_char(group_id)
from group_
UNION ALL
select to_char(group_id) || '-' || replace (sys_connect_by_path(to_char(set_id), ' '), ' ', '')
from set_
    cross join group_
connect by prior set_id < set_id

Upvotes: 2

Related Questions