Split a column with multiple IDs into multiple rows

If the following table structure is used, how can we split multiple rows and correspond to employee numbers?

 

First, create a test table.

Create Table Department (ID int identity (500) primary key, departmentname varchar (50) null, members nvarchar () null) Create Table employee (ID int identity () primary key, name nvarchar (50) null) insert into Department values ('administrative ', '1,'), ('sales', '42 '), ('market', '13, 17,27, 28,43 ') Select * From DBO. department

 

 

The data in the employee table can be directly generated using an SQL generator ~

-- Create Table # num (id int) insert into # num (ID) Select top 500 row_number () over (order by (select 1 )) from sys. objects A, sys. objects B

 

 

-- Use a CTE to write code for convenience.
; With cte1 as (select. ID,. departmentname,. members, B. ID as pos, charindex (',',. members, B. ID) as nextsplit from DBO. department a inner join # num B on substring (',' +. members, B. ID, 1) = ',' and B. ID <= Len (. members), cte2 as (select ID, departmentname, case when nextsplit <> 0 then substring (members, POs, nextsplit-Pos) else right (members, Len (Members) -pos + 1) end as employeeid from cte1) Select. *, B. name from cte2 a inner join DBO. employee B on. employeeid = B. ID

 

 

PS: The above statement actually has a lot of room for optimization. Here we just provide a method ~ For the moment, we will not be entangled in efficiency ~ Second (second 3 rows) Second

Split a column with multiple IDs into multiple rows