应用场景：将项目组的员工职位合并到同一列

/*
应用场景：将项目组的员工职位合并到同一列
*/
IF OBJECT_ID('A') IS NOT NULL
  DROP TABLE A
GO

CREATE TABLE A
(
ID INT,
NAME VARCHAR(50)
)
GO

IF OBJECT_ID('B') IS NOT NULL
  DROP TABLE B
GO

CREATE TABLE B
(
NAME VARCHAR(20)
)
GO

INSERT INTO A
SELECT 1,'张三，李四'
UNION ALL
SELECT 2,'王五'
UNION ALL
SELECT 3,'马六，朱七'
GO

INSERT INTO B
SELECT '张三'
UNION
SELECT '朱七'
GO

SELECT ID,C.NAME,GADRES=(CASE WHEN NAME1=B.NAME AND NAME1 IS NOT NULL THEN '1'
                      WHEN B.NAME IS NULL THEN '' ELSE '0' END)
                +(CASE WHEN B.NAME IS NULL THEN '' ELSE ',' END)
                +(CASE WHEN NAME2=B.NAME THEN '1' ELSE '0' END)
FROM
(
SELECT ID,NAME,NAME1=SUBSTRING(NAME,0,CHARINDEX(',',NAME))
,NAME2=SUBSTRING(NAME,CHARINDEX(',',NAME)+1,LEN(NAME)-CHARINDEX(',',NAME)+1)
FROM A
) C
LEFT JOIN B
ON C.NAME1=B.NAME OR C.NAME2=B.NAME
GO