SQL Server中用STUFF+CHARINDEX实现关联表列值去重逗号拼接

需求回顾 #

现有两张表t1和t2，结构数据如下：

表t1：

col1    Col2    
================
xyz       ab/cde 
pqr       uv/wxy 
xyz       fg/hij     
lmn       rtg
pqr       12/456

表t2：

col1        
======
xyz         
pqr   

需要实现：

1. 仅关联t1和t2中col1值匹配的记录；
2. 提取t1对应Col2字段中斜杠（/）之前的内容，无斜杠则取整个字段；
3. 对提取内容去重后，以逗号分隔聚合填充到t2的Col2列，最终输出：

col1    Col2    
================
xyz     ab,fg
pqr     uv,12

补充要求：若t1中存在重复的提取值（如fg多次出现），需仅保留唯一值。

实现代码 #

SELECT 
    t2.col1,
    Col2 = STUFF(
        (
            SELECT DISTINCT ',' + LEFT(t1.Col2, ISNULL(NULLIF(CHARINDEX('/', t1.Col2) - 1, -1), LEN(t1.Col2)))
            FROM t1
            WHERE t1.col1 = t2.col1
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'),
        1,
        1,
        ''
    )
FROM t2
ORDER BY t2.col1;

代码说明 #

• 提取斜杠前内容：
  • CHARINDEX('/', t1.Col2)：定位斜杠在Col2中的位置，无斜杠时返回0；
  • NULLIF(CHARINDEX('/', t1.Col2) - 1, -1)：处理无斜杠的情况，将-1转换为NULL；
  • LEFT(t1.Col2, ISNULL(..., LEN(t1.Col2)))：有斜杠则取斜杠前部分，无斜杠则取整个Col2值。
• 去重与聚合：
  • DISTINCT：确保提取的内容唯一，满足重复值去重要求；
  • FOR XML PATH(''), TYPE：将多行提取结果拼接为XML字符串，TYPE避免特殊字符被转义；
  • .value('.', 'NVARCHAR(MAX)')：将XML转换为普通字符串；
  • STUFF(..., 1, 1, '')：移除字符串开头多余的逗号。

测试补充场景 #

当t1数据为：

col1    Col2    
================
xyz       ab/cde 
pqr       uv/wxy 
xyz       fg/hij     
lmn       fg
pqr       fg/456

执行上述代码后，输出结果为：

col1    Col2    
================
xyz     ab,fg
pqr     uv,fg

符合去重要求。

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