I have a database with 3 tables; Employees, Clients, and a linking table. The Employees and Clients tables have a many-to-many relationship. One or more employee(s) can be working with one or more client(s). Some employees (generally new employees) may not be working with any clients. The Employees table has the following fields; Employoee_ID, Employee_FirstName, Employee_LastName The Clients table has the following fields; Client_ID, Client_Name, Client_Address.

I need to write a SQL query on Microsoft SQL that selects Employoee_ID, Employee_FirstName, Employee_LastName, then has a Clients field which lists all of the clients ID's that each employee is working with in one single field.

Tables:

Employees:

Clients:

Linking Table

I need a Microsoft SQL Query that will give the following output; (Each employee is listed ONLY once and all of his/ her related clients' IDs are shown in the same row, in one field, as comma seperated/ semicolon separated values.

Please advise if you know of a way to do write a query to get the results above. Currently, I am getting each employee multiple times with each client ID in separate rows.

I tried using SELECT distinct & other functions but couldn't generate comma separated client ID's in one field for each employee.

Currently my query is listing Employee "John Adams" twice, once with each of its clients.

Tried select (distinct) EmployeeID to get each employee only listed once but didn't work in listing all related clientID's in one field.

CodePudding user response：

You could use string_agg - STRING_AGG (Transact-SQL)

select e.employee_id,
       e.employee_firstname,
       e.employee_lastname,
       string_agg(cli.clientid, ',') AS clients
  from Employees e
left join [linking table] cli
  on e.employee_id= cli.employeeid;

CodePudding user response：

Since SQL Server 2017 you can achieve the desired output via group by and STRING_AGG function:

select 
  e.Employoee_ID,
  MAX(e.Employee_FirstName) as Employee_FirstName,
  MAX(e.Employee_LastName) as Employee_LastName,
  STRING_AGG(CONVERT(NVARCHAR(max),ClientID), ';') as Clients
from employoee e
join links l on e.Employoee_ID = l.EmployeeID
join client c on c.Client_ID = l.ClientID
group by e.Employoee_ID

Output:

Check out the demo fiddle.