Concatenate each column value into a string for a subquery -

all i've done lot of background reading on over , off past day or , i'm none wiser on how achieve this.

i have looked on site , found ways of concatenating multiple rows 1 column i'm after bit more bespoke, please help...

i have 2 tables - 1 list of people , details them such name etc , person reference.

the second contains number of alerts person, 1 person can have multiple alerts. contain person reference , type of alert have in string.

i want join these 2 tables using inner join on person reference. next want find of alerts each person , concatenate string , show "all alerts" column.

so end following output:

first name | surname | alerts -----------+---------+--------------------------     tony       | stark   | alert 1, alert 2, alert 3 

i can far going through of alerts in alerts table , put alerts every person string, need concatenated value each person, , haven't figured out how this.

i've spent day on , looked xmlpath solutions , using cte, cross apply , subqueries specify clause. little lost.

declare @concatenatedvals varchar (255)  set @concatenatedvals =     (         declare @allalerts varchar(8000)           select @allalerts =  coalesce(@allalerts + ', ', '') + personalert         alerts          select @allalerts 'all alerts'     )    

i found solution posted here:

https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/#toc205129480

this referenced solution adam machanic

also, stuff function, not seen before:

select p1.categoryid, stuff( (select ','+productname northwind.dbo.products p2 p2.categoryid = p1.categoryid order productname xml path(''), type).value('.', 'varchar(max)') ,1,1,'') products northwind.dbo.products p1 group categoryid ;