create table table_a
(col1 varchar(100),
col2 varchar(100))
go
insert into table_a values ('item1','item2')
insert into table_a values ('item2','item3')
insert into table_a values ('item3','item4')
insert into table_a values ('item5','item6')
insert into table_a values ('item6','item7')
insert into table_a values ('item8','item9')
go
select * from table_a
go
/*
As per the above data
item1 is linked with item2
and item2 is linked with item3
and item3 is linked with item4
so all item1 , item2 , item3 and item4 are in same group, lets give it number 1.
item5 is linked with item6
and item6 is linked with item7
so item5 , item6 and item7 are in same group, lets give it number 2
item8 and item9 will be in group 3.

We can achieve this result using follwing query.
*/

WITH temp (Col1, Col2, id)
  AS   (SELECT Col1, Col2,
               ROW_NUMBER() OVER (ORDER BY Col1, Col2) AS num
        FROM   TABLE_A) ,

 t as
(select col1 , col2,id,1 as linkid from temp where id= 1
union all
select temp.col1 , temp.col2,temp.id, case when
(temp.col1 in (t.col1, t.col2) or
temp.col2 in (t.col1, t.col2) ) then linkid
else linkid+ 1 end
 from temp , t
where  temp.id= t.id+1
)

select col1, col2 , linkid from t

 

Regards

Amish Shah