Can this be done in SQL? Find the transitive relation

Dear Group,

I am strugling get the following done in SQL. Could anyone help me to figure out a way?

Oracle version 8i .

create table t_grp (id1 char(1), id2 char(2)) ;

insert into t_grp values ('A','C');	--> A:C are related
insert into t_grp values ('B','D');	--> B:D are related
insert into t_grp values ('F','H');
insert into t_grp values ('G','H');
insert into t_grp values ('B','G');
insert into t_grp values ('X','Y');
insert into t_grp values ('W','Y');

I want to group the values based on the following rules,

1, if any of the value from id1 or id2 can be linked to any other values,
they all will be treated as one group

Ex, in the above case
A has relationship to C, neither of them has any other relationship to any
 other values in the table
B has relationship to D, B has relationship to G, G has relationship to H, H
 has relationship to F
F has relationship to H, H has relationship to G ...

A:C				- Group 1 (A,C) - new elements
B:D -> B:G -> G:H -> H:F 	- Group 2 (B, D, G, H, F)
F:H -> H:G -> G:B -> B:D 	- Group 2 (F, H, G, B, D) - same elements as
above
G:H -> G:B -> H:F -> B:D 	- Group 2 (G, H, B, F, D) - do -
G:B -> G:H -> H:F -> B:D 	- Group 2 (G, B, H, F, D) - do -
X:Y -> Y:W			- Group 3 (X, Y, W) - new set of elements

My objective is to get the final result in the below form.

grp 	id1	id2
-----	------	------
1	A	C
2	B	D
2	F	H
2	G	H
2	B	G
3	X	Y
3	W	Y

The following output is also fine [This would be the final output, but I can covert from the above to this one]

grp	id
----	-------
1	A
1	C
2	B
2	D
2	F
2	H
2	G
...
3	X
3	Y
3	W
3	Y

Thank you. Received on Sat Aug 04 2007 - 14:06:55 CDT