Creating a recursive query in SQL

Question

I have data like this:

• Person A has a relationship with Person B, and person A has a relationship with Person C
• Person B has a relationship with D and E.

I want to view result in group in SQL Server (A, B ,C,...) and (B,D,E,...)

I have tried looking recursive but not getting this to implement.

I need to do this in SQL.

Thanks for the help .

Answer 1

you can achieve without using recursive cte. Try the following using string_agg and concat. here is the demo.

select
  concat(columnA, ', ', string_agg(columnB, ', ')) as columnC
from myTable
group by
  columnA

output:

|columnC|
*-------*
|A, B, C|
|B, D, E|

In SQL Server 2012 you can use XML PATH as following

select
  concat(
  columnA, ',',
  stuff((
            select ', ' + columnB
            from myTable m1
            where m1.columnA = m2.columnA
            for xml path('')
        ), 1, 1, ''
    )) as columnC
from myTable m2
group by
  columnA