Concatenating results in a comma separated list I am trying to list all of the employees linked to a certain 'Interaction' I want the employees listed on one line with comma's separating them, I have the following query which works but is very slow.

SELECT CONVERT(VARCHAR(254), COALESCE(STUFF((
SELECT ', ' + LTRIM(RTRIM(REPLACE(emp1.Full_Name,CHAR(9),''))) 
FROM Employee emp1 WITH (nolock) 
JOIN Rn_Int_Employee rie WITH (nolock) on emp1.Employee_Id = rie.Employee_Id
WHERE rnint.Rn_Interactions_Id = rie.Rn_Interactions_Id 
GROUP BY emp1.Full_Name 
ORDER BY emp1.Full_Name 
FOR XML PATH (''),TYPE ).value('text()[1]', 'nvarchar(max)'), 1, 2, '')
,STUFF((
SELECT ', ' + LTRIM(RTRIM(REPLACE(emp1.Full_Name,CHAR(9),''))) 
FROM Employee emp1 WITH (nolock) 
WHERE rnint.Rn_Create_User=emp1.Rn_Employee_User_Id
GROUP BY emp1.Full_Name 
ORDER BY emp1.Full_Name 
FOR XML PATH (''),TYPE ).value('text()[1]', 'nvarchar(max)'), 1, 2, ''))) 
Employees    
FROM Rn_Interactions rnint


Does anyone have any recommendations for a more efficient way of doing this?
Concatenating strings from rows in to a single field is an age-old quandary among the SQL Server community unfortunately.

In SQL Server 2017, Microsoft introduced the STRING_AGG function which has performance benefits over the "stuff XML" method you're using above. It also looks a lot cleaner, which is nice. Example:

SELECT 
    i.Rn_Interaction_Id, 
    STRING_AGG(e.Full_Name, ', ') WITHIN GROUP (ORDER BY e.Full_Name) as Employees
FROM Rn_Interactions i
    LEFT JOIN Rn_Int_Employee ie ON i.Rn_Interactions_Id = ie.Rn_Interactions_Id
    LEFT JOIN Employee e1 ON ie.Employee_Id = e1.Employee_Id
GROUP BY Rn_Interaction_Id


(Haven't actually tested that, but it shows you how you might use STRING_AGG)

If you haven't got and can't get SQL Server 2017 though, tough luck unfortunately. One solution might be to tackle the root of the problem: Create a field on the Rn_Interactions table that is meant to store this string, then re-write the string to the table every now and then (maybe when your app saves the interaction or changes the employees it's linked to). Then when you want a list of employees, it's right there already in the table, rather than having SQL re-generate it every time you select. It's far from a normalised and perfect solution, but hey, life is neither of those things.