Is there any function which would help me in concatenating second table ids without repeating rows of first table in SQL

Question

with cte (PersonID, FirstName, name) 
as
(
    select 
        p.PersonID, p.FirstName, dep.name 
    from 
        [PersonDepMapping] map 
    inner join 
        person p on map.personid = p.PersonID
    inner join 
        Department dep on dep.DepartmentID = map.DepartmentID
)
select * 
from cte

This is what I am getting now:

1   Kim     English
1   Kim     Economics
2   Gytis   Engineering
3   Peggy   Mathematics

I want an output something like this:

1   Kim     English, Economics
2   Gytis   Engineering
3   Peggy   Mathematics

Can anyone please help? I couldn't find any answer that could help me get what I need - do I need to use GROUP BY or FOR XML PATH or something else entirely?

Does this answer your question? [Optimal way to concatenate/aggregate strings](https://stackoverflow.com/questions/13639262/optimal-way-to-concatenate-aggregate-strings) — Amit Verma, Aug 21 '21 at 10:49

score 2 · Accepted Answer · answered Aug 21 '21 at 10:49

2

You are looking for string_agg() (available since SQL Server 2017):

select p.PersonID, p.FirstName,
       string_agg(dep.name, ',')
from PersonDepMapping map inner join
     person p
     on map.personid = p.PersonID join
     Department dep
     on dep.DepartmentID = map.DepartmentID
group by p.PersonID, p.FirstName;

answered Aug 21 '21 at 10:49

Gordon Linoff

1,242,037
58
646
786

What if i am having multiple joins and many select items then adding all items in group by will cause a problem In that case what I can do? – devloper Aug 21 '21 at 11:13
@devloper . . . There should be no problem. This is just another aggregation function. – Gordon Linoff Aug 21 '21 at 11:27
Lost the "Close as Duplicate" button again? – Charlieface Aug 21 '21 at 23:43

Is there any function which would help me in concatenating second table ids without repeating rows of first table in SQL

1 Answers1