SQL Server

/* How to get linked values in one group using CTE*/ 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