SQL server: Question about query group and auto id

i have a table which has 2 columns like this, picture is input and output:

Explain input: 2 column is 2 person who relation ship together. Exam: A relation with B, C,D,H Output: i want to merge 2 column , with Column group ID auto and column RelationShip

• ID group auto: i tried query: row_number() OVER (ORDER BY [columnA]) n
• RelationShip: is group all person relatioship together and group them to 1 ID group. Exam person A,B,C,D,H realation ship together and group id auto is 1

I have a demo in https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=f81ff386b07589654ad133a8e4b30472

In my query, first i turn the data into a single column, second i count letter, next step i don’t know query.

I tried recursive CTE but error (perhaps I don’t understand clearly about recursive)

I want to solve this problem in sql query.

Answer

You can use a recursive CTE to assign each “column” value a number. This starts by created edges in both directions and then following them:

with pairs as (
      select columnA, columnB from myTable
      union -- on purpose to remove duplicates
      select columnB, columnA from myTable
     ),
     cte as (
      select distinct columnA, columnA as columnB,
             convert(varchar(max), ',' + columnA + ',') as visited,
             1 as lev
      from pairs
      union all
      select cte.columnA, p.columnB, concat(visited, p.columnA, ','), lev + 1
      from cte join
           pairs p
           on cte.columnB = p.columnA
      where cte.visited not like concat('%,', p.columnB, ',%') and
            lev < 5
     )
select cte.columnA, min(cte.columnB),
       dense_rank() over (order by min(cte.columnB))
from cte
group by cte.columnA;

You can then join this in to assign a group id:

with pairs as (
      select columnA, columnB from myTable
      union -- on purpose to remove duplicates
      select columnB, columnA from myTable
     ),
     cte as (
      select distinct columnA, columnA as columnB,
             convert(varchar(max), ',' + columnA + ',') as visited,
             1 as lev
      from pairs
      union all
      select cte.columnA, p.columnB, concat(visited, p.columnA, ','), lev + 1
      from cte join
           pairs p
           on cte.columnB = p.columnA
      where cte.visited not like concat('%,', p.columnB, ',%') and
            lev < 5
     ),
     groups as (
      select cte.columnA, min(cte.columnB) as min_columnB,
             dense_rank() over (order by min(cte.columnB)) as group_id
      from cte
      group by cte.columnA
     )
select t.*, g.group_id
from mytable t join
     groups g
     on g.columnA = t.columnA;

Here is a db<>fiddle.