How do you concatenate strings per group in SQL Server?

Short answer: Use STRING_AGG(expression, separator) WITHIN GROUP (ORDER BY ...) on modern SQL Server. Older trick: FOR XML PATH.

Sample solution

SELECT DepartmentId,
       STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name) AS Employees
FROM Employees
GROUP BY DepartmentId;

Mention STRING_AGG availability (SQL Server 2017+) if the environment might be older.