SQL server a表ids存在多个id以逗号分隔的字段，求对应b表中的字段同样以逗号分隔

a表
Id 1,2,3
AdvId 1,3,5
CustomerIds ‘1,2’,‘3,5’,‘2,4’
b表
Cust_CustomerID 1,2,3,4,5,6,7,8,9
CusttomerName ‘鸣人’,‘佐助’,‘自来也’,‘纲手’,‘大蛇丸’,‘我爱罗’,‘奇拉比’,‘鼬’,‘长门’

SELECT a.Id,c.CustomerName,a.CustomerIds, stuff(
(
	SELECT ','+b.CustomerName 
	FROM Pact_Customer as b 
	WHERE ','+a.CustomerIds+',' LIKE '%,'+CAST(b.Cust_CustomerID AS varchar(50))+',%'
	FOR XML PATH('')
) ,1,1,'') AS CustomerNames
FROM Pact_Adv as a left join Pact_Customer as c on a.AdvId=c.Cust_CustomerID

参考资料sql server2008数据库一个表字段中存了id，并以逗号分隔，id对应的详细信息在另一个表中

USE tempdb
GO
IF OBJECT_ID('a') IS NOT NULL DROP TABLE a
IF OBJECT_ID('b') IS NOT NULL DROP TABLE b
CREATE TABLE a([序号] int,[主管领导] VARCHAR(300))
CREATE TABLE b([序号] int,[姓名] NVARCHAR(300))
INSERT INTO a ([序号] ,[主管领导]) VALUES (1,'1,2,3')
INSERT INTO a ([序号] ,[主管领导]) VALUES (2,'2,4')
INSERT INTO b ([序号],[姓名]) VALUES (1,N'李一')
INSERT INTO b ([序号],[姓名]) VALUES (2,N'刘二')
INSERT INTO b ([序号],[姓名]) VALUES (3,N'张三')
INSERT INTO b ([序号],[姓名]) VALUES (4,N'李四')
 
SELECT a.[序号],a.[主管领导], stuff(
(
	SELECT ','+b.[姓名] 
	FROM b 
	WHERE ','+a.[主管领导]+',' LIKE '%,'+CAST(b.[序号] AS varchar(50))+',%'
	FOR XML PATH('')
) ,1,1,'') AS [主管领导姓名]
FROM a
 
/*
序号	主管领导	主管领导姓名
1	    1,2,3	    李一,刘二,张三
2	    2,4	        刘二,李四
*/

男儿不展风云志，空负天生八尺躯。