Need help constructing a query to group related elements

Question

I have a table containing the IDs of elements that are related.

ID1 ID2
A   B
A   C
B   D
B   C
E   F
G   D
G   C
H   I
D   C

The example contains the following groups:

A,B,C,D,G
E,F
H,I

Since A is connected to B,C, B is connected to C,D and D is connected to G. E,F and H,I are only related to each other.

Is it possible to find these groups using SQL? Not sure what the output of the SQL would be, maybe something like this:

  ID    group
    A   1
    B   1
    C   1
    D   1
    G   1
    E   2
    F   2
    H   3
    I   3

Probably some form of hierarchical query will do the trick but those usually baffle me. As long as I can discriminate between groups.

Answer 1

Here is what I found:

select root2 || ', ' || listagg(id1, ', ') within group (order by id1) grp
from
(
  select id1, max(root2) keep (dense_rank last order by lev) root2
  from
  (
    select t.*, connect_by_root id2 root2, level lev
    from <my_table> t
    connect by prior t.id1 = t.id2
  )
  group by id1
)
group by root2
;

This gives:

**GRP**
C, A, B, D, G
F, E
I, H