Concat rows for employee roles

I’m using SQL Server. Looking for similar functionality as GROUP_CONCAT or listagg functions provided in other Databases. Seems like STUFF function is provided to concat rows. Here’s my attempt.

SELECT Employee.Id, Employee.First,
    STUFF((
        SELECT '' + Role.Id
        FROM Role
        WHERE EmployeeRole.RoleId = Role.Id
        FOR XML PATH('')), 1, 0, '') AS RoleIds,
From Employee
  INNER JOIN EmployeeRole ON Employee.Id = EmployeeRole.EmployeeId
  INNER JOIN Role ON EmployeeRole.RoleId = Role.Id
  ORDER BY Employee.Id;

Employee

+----+-------+------+
| Id | First | Last |
+----+-------+------+
|  1 | John  | Doe  |
|  2 | Jane  | Doe  |
+----+-------+------+

Role

+----+-------+
| Id | Name  |
+----+-------+
|  1 | Role1 |
|  2 | Role2 |
|  3 | Role3 |
|  4 | Role4 |
+----+-------+

EmployeeRole

+------------+--------+
| EmployeeId | RoleId |
+------------+--------+
|          1 |      1 |
|          1 |      2 |
|          2 |      2 |
|          2 |      3 |
|          2 |      4 |
+------------+--------+

Expected Output

+------------+-------+---------+-------------------+
| EmployeeId | First | RoleIds |     RoleNames     |
+------------+-------+---------+-------------------+
|          1 | John  | 1,2     | Role1,Role2       |
|          2 | Jane  | 2,3,4   | Role2,Role3,Role4 |
+------------+-------+---------+-------------------+

Answer

In SQLServer 2017, you can use aggregate function STRING_AGG():

SELECT 
    e.Id EmployeeId, 
    e.First,
    STRING_AGG(r.Id, ',') WITHIN GROUP(ORDER BY r.Id) RoleIds,
    STRING_AGG(r.Name, ',') WITHIN GROUP(ORDER BY r.Id) RoleNames
FROM Employee e
INNER JOIN EmployeeRole er ON e.Id = er.EmployeeId
INNER JOIN Role r ON er.RoleId = r.Id
GROUP BY e.Id, e.First
ORDER BY e.Id;

Note: table aliases make the query more concise and easier to read. I modified the query to use them.