Guest Guest - 28 days ago 9
SQL Question

SQL comma separated column loop

I have a serious problem with SQL that already took me 3 hours. I have two tables like these:

First table: Employees

ID | NAME
---+--------
1 | John
2 | Mike
3 | Robert


Second table: Customers

ID | NAME | EMPLOYEES
---+---------+--------------
1 | Michael | 2,3
2 | Julia | 1
3 | Mila | 1,2,3


I want the output like this:

Michael | Mike, Robert
Julia | John
Mila | John, Mike, Robert


What should the SQL command to get the expected output?

Answer
Select A.Name
      ,Employees  = (Select Stuff((Select Distinct ',' +Name From Employees Where charindex(','+cast(ID as varchar(25))+',',','+A.EMPLOYEE_ID+',')>0 For XML Path ('')),1,1,'') )
 From Customers A

Returns

Name    Employees
Michael Mike,Robert
Julia   John
Mila    John,Mike,Robert