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 .
Advertisement
Answer
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