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 .

zealous · Accepted Answer · 2020-06-09 03:38:44Z

2

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

edited Jun 9, 2020 at 3:38

answered Jun 9, 2020 at 2:49

zealous

7,5434 gold badges20 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Danh Tran Quoc Over a year ago

tks your help. Can you help me replace string_agg in sql server 2012 or 2008?

Danh Tran Quoc Over a year ago

tks for your help

Collectives™ on Stack Overflow

Creating a recursive query in SQL

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related