Merging Two Rows into one in SQL Server 2016

Question

I know this question has been asked and answered but I just can't seem to get it correct. The following query display two records (UUID, and Role Name). I would like this to be 1 record, with a comma-delimited list of role names in one column.

SELECT    
    EMP.Employee_UUID, ER.Role_Name
FROM
    Employees_To_Roles AS E2R 
INNER JOIN
    Employee_Roles AS ER ON E2R.Role_ID = ER.Role_ID 
                         AND E2R.App_id = ER.App_id 
INNER JOIN
    Employees EMP ON E2R.Employee_UUID = EMP.Employee_UUID
WHERE     
    (E2R.App_id = 2)

ID                  Roles
---------------------------------
1000                Employee
1000                Developer

I'm trying to get the following:

ID                  Roles
---------------------------------------
1000                Employee, Developer

Answer 1

You can do it using GROUP BY and STRING_AGG :

SELECT    EMP.Employee_UUID, STRING_AGG(ER.Role_Name, ',')
FROM      Employees_To_Roles AS E2R INNER JOIN
          Employee_Roles AS ER ON E2R.Role_ID = ER.Role_ID 
                               AND E2R.App_id = ER.App_id INNER JOIN
          Employees EMP ON E2R.Employee_UUID = EMP.Employee_UUID
WHERE     E2R.App_id = 2
GROUP BY EMP.Employee_UUID

For versions before SQL Server 2017, you will need to use the FOR XML PATH :

SELECT 
    EMP.Employee_UUID,
    STUFF ((SELECT ',' + ER.Role_Name
            FROM  Employees_To_Roles AS E2R 
            INNER JOIN Employee_Roles AS ER ON E2R.Role_ID = ER.Role_ID 
                                            AND E2R.App_id = ER.App_id
            WHERE E2R.Employee_UUID = EMP.Employee_UUID AND E2R.App_id = 2
            FOR XML PATH('')), 1, 1, '') AS Role_Names
FROM 
    Employees EMP
GROUP BY
    EMP.Employee_UUID