Concatenate rows from table into a single text string SQL Server

SQL Server > XML Data > FOR XML > Concatenate rows into text lines

create table #person
(
       id           int,
       name   nvarchar(50)
)
insert into #person(id, name)
values (1,'john')
insert into #person(id, name)
values (1,'smith')
insert into #person(id, name)
values (2,'laura')
insert into #person(id, name)
values (2,'stan')

select * from #person

1      john

1      smith
2      laura
2      stan

select distinct

       p2.id,
    substring((Select ','+ p1.name   AS [text()] From #person p1 Where p1.id = p2.id
                ORDER BY p1.id For XML PATH ('')),2, 1000) name
         From #person p2

1 john,smith

2 laura,stan
drop table #person