SQL Server 2016拆分关联字段后拼接员工姓名问题求助

原始数据库数据格式如下：

其中ID对应关系：

• 1395/3003 = Cat Stevens/Therapist
• 1399/1388 = Dog Stevens/Program Staff

需要将员工姓名以逗号分隔拼接，期望结果：

需求说明：拆分/分隔的ID，关联StaffContacts表获取姓名后重新拼接成一行。使用SQL Server 13（2016），无法使用STRING_SPLIT函数。

当前编写的代码执行后Staff字段为NULL：

WITH CTE                        
AS                           
  (SELECT                            
  DocId                          
  ,Split.a.value('. ', 'VARCHAR(100)') 'Staff'
   /* Separate into separate columns */                          
  FROM                               
    (SELECT DocId, CAST ('<M>' + REPLACE(Staff, ',', '</M><M>') + '</M>' AS XML) AS Data
     FROM CustomerDocument cd                                               
    ) AS B                       
    CROSS APPLY Data.nodes ('/M') AS Split(a) /* Unpivot into rows so can join to tables below */   
)   


/* Get Staff */                          
(SELECT DISTINCT stafftab.DocId                              
  ,[Staff] = STUFF(/* This is added to delete the leading ', ' */                            
     (SELECT DISTINCT ', '  + ([FirstName]+ ' ' + [LastName])                   
        FROM cte                     
        JOIN Documents d on cte.DocId = d.DocId                  
        JOIN StaffContacts sc ON d.ClientId = sc.ClientId                                   
    WHERE sc.Relationship in (                  
            SELECT CodeId               
            FROM Codes co 
            Where categorycode = 'Staff')       
            AND cte.DocId = stafftab.DocId               
     FOR XML PATH ('')) /* Concatenate multiple rows of data into comma separated values back into one column */                        
     , 1, 2, '')   /* At 1st character, delete 2 which deletes the leading ', ' */                       
         FROM cte stafftab
)

尝试拆分/前的ID，但无法成功关联StaffContacts表并重新拼接：

SELECT DocId,
LEFT(Staff, charindex('/', Staff)-1)
FROM CustomerDocument cd

问题出在原CTE仅拆分了逗号分隔的项，但未进一步拆分每个项中的/提取StaffID，且关联逻辑有误。以下是修正后的实现：

WITH SplitComma AS (
    -- 第一步：拆分逗号分隔的Staff/Relationship项
    SELECT 
        cd.DocId,
        TRIM(Split.a.value('.', 'VARCHAR(100)')) AS StaffRel
    FROM CustomerDocument cd
    CROSS APPLY (
        SELECT CAST('<M>' + REPLACE(cd.[Staff/Relationship], ',', '</M><M>') + '</M>' AS XML) AS Data
    ) AS B
    CROSS APPLY B.Data.nodes('/M') AS Split(a)
),
SplitSlash AS (
    -- 第二步：拆分每个项中的/，提取StaffID和RelationshipID
    SELECT 
        DocId,
        -- 提取/前的StaffID
        LEFT(StaffRel, CHARINDEX('/', StaffRel) - 1) AS StaffID,
        -- 提取/后的RelationshipID（可选，用于过滤关系类型）
        RIGHT(StaffRel, LEN(StaffRel) - CHARINDEX('/', StaffRel)) AS RelationshipID
    FROM SplitComma
)
-- 第三步：关联StaffContacts获取姓名，再拼接成逗号分隔字符串
SELECT 
    ss.DocId,
    STUFF(
        (SELECT ', ' + sc.FirstName + ' ' + sc.LastName
         FROM SplitSlash ss_inner
         JOIN StaffContacts sc ON ss_inner.StaffID = sc.StaffID -- 根据实际表结构调整关联字段
         -- 若需过滤关系类型，添加以下条件：
         -- JOIN Codes co ON ss_inner.RelationshipID = co.CodeId AND co.categorycode = 'Staff'
         WHERE ss_inner.DocId = ss.DocId
         FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'),
        1, 2, ''
    ) AS Staff
FROM SplitSlash ss
GROUP BY ss.DocId;

关键修正点： #

1. 双层拆分：先拆分逗号分隔的整体项，再拆分每个项中的/，分离出StaffID和RelationshipID；
2. 正确关联：直接用拆分出的StaffID关联StaffContacts表（需根据实际表结构调整关联字段，比如若StaffContacts主键是ID，改为ss_inner.StaffID = sc.ID）；
3. 避免NULL：用TRIM清理拆分后的空白，确保ID匹配；使用TYPE和.value()处理XML拼接，避免特殊字符转义。

异常处理补充： #

若Staff/Relationship存在无/的异常数据，添加判断避免报错：

LEFT(StaffRel, CASE WHEN CHARINDEX('/', StaffRel) > 0 THEN CHARINDEX('/', StaffRel) - 1 ELSE LEN(StaffRel) END) AS StaffID

内容的提问来源于stack exchange，提问作者WixLove