Guest Guest - 27 days ago 9
SQL Question

SQL comma seperated column loop

I've a serious problem with SQL that take my 3 hours.

I have 2 table like this;

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



Finally what should the SQL command?

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