SQL Server : combine multiple rows

Question

I have an EMPLOYEE table that references the roles assigned to an employee, and the skills the employee has. I am trying create a SELECT statement using SQL Server that will return, per row, the Employee #, Employee Login, Employee Name, Facility, Work Center, Roles and Skills. The roles are referenced in another table as are the skills.

My issue is that I cannot seem to write a SQL query such that an employee with multiple roles and/or skills would have each skill listed per employee record.

My current SQL only retrieves one role/skill per row.

SELECT 
    e.EmployeeNo, e.Name, e.LoginName, f.Facility, w.WorkCenter, 
    r.Role AS Roles, s.Name as Skills
FROM
    EMPLOYEE e
LEFT JOIN 
    EMPLOYEE_FACILITY f ON e.ID = f.EmployeeID
LEFT JOIN 
    EMPLOYEE_WORK_CENTER w ON e.ID = w.EmployeeID
JOIN 
    EMPLOYEE_ROLE er ON e.ID = er.EmployeeID
LEFT JOIN 
    ROLE r ON er.ID = r.ID
JOIN 
    EMPLOYEE_SKILL es ON e.ID = es.EmployeeID
LEFT JOIN 
    SKILL s ON es.ID = s.ID
GROUP BY
    e.EmployeeNo, e.Name, e.LoginName, f.Facility, w.WorkCenter, 
    r.Role, s.Name
ORDER BY
    e.EmployeeNo

The output I'm looking for is:

Employee #	Employee Name	loginName	Facility	WorkCenter	Roles	Skills
123456789	Test	TestLogin	ABC1	MACHINING	IT	CPP
					ADMIN	SCRUM
234567890	TestUser	Test2	DEF2	STAMPING	FAB	LABOR

As you can see, I'm getting multiple rows for each Employee #, I'm trying to combine all the roles and skills for a given employee into a single row.

EDIT: Is it possible to combine the rows with JOINs? I am rather rusty in SQL, and the last time I used MySQL was years ago.

Answer 1

As you know, joining a record to an additional table with multiple matching items will create additional rows in the result set, where the record from the primary table is now repeated for each matching record in the additional table.

Accounting for this is relatively easy in SQL Server 2017 and later using string_agg(), and still possible in prior versions using the awkward STUFF() + FOR XML PATH hack. Typically, though, the better option is aggregating this data in the client code or reporting tool.

However, we have an additional wrinkle here. You're not adding only one additional table with multiple matches... you're adding two. Every additional table you bring in this way not only duplicates the record the from the primary table, but also all of the matching records for each additional table that came before it, in a way that can quickly multiply the number of records.

The result is someone who has roles IT and Admin and Fab, when matched to skills CPP and SCRUM, will have at least 6 rows in the results. Add another table with two matches, and it would 12 rows.

Therefore, whether you use string_agg() or STUFF()/FOR XML PATH, you also want to pre-aggregate your matched data separately, before joining everything together. The and easiest way to do this is in a Common Table Expression (CTE) for each table (though a nested SELECT can also work), where the CTE results only include the key field(s) from the primary table and rolled-up additional items.