如何在 T-SQL 中拆分由逗号分隔的重复字符串

How to split repeating string delimated by commas in T-SQL(如何在 T-SQL 中拆分由逗号分隔的重复字符串)

问题描述

我已经尝试了一些试验，但仍然无法弄清楚.任何帮助将不胜感激.

I have tried some trials and can't figure it out yet. Any help would be appreciated.

我有一张如下所示的表格.

I have a table like below.

LocationID       Project Name
1                A,A
1                A
1                A,B,C
1                A,C
1                B,C
1                C
2                A
2                C,D,E
2                E,F
2                F
3                G,H
3                H

我要找的结果是.它删除所有重复并仅保留具有关联 ID 的不同值.

The result I am looking for is. It removes all the repeatings and keep only distinct values with their associated ID.

LocationID       Project Name
1                A
1                B
1                C
2                A
2                C
2                D
2                E
2                F
3                G
3                H

我试图通过 (len(replace(@ProjectNames, ','))) 来计算逗号的数量

I tried to count the number of commas by (len(replace(@ProjectNames, ','))

使用 http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx

但是，仍然没有运气.. 任何帮助将不胜感激.谢谢.

However, still no luck.. Any help would be appreciated. Thank you.

推荐答案

declare @T table(LocationID int, PName varchar(50))

insert into @T values
(1,                'A,A'),
(1,                'A'),
(1,                'A,B,C'),
(1,                'A,C'),
(1,                'B,C'),
(1,                'C'),
(2,                'A'),
(2,                'C,D,E'),
(2,                'E,F'),
(2,                'F'),
(3,                'G,H'),
(3,                'H')

select distinct
  T.LocationID,
  r.value('.', 'varchar(50)') as [Project Name]
from @T as T
  cross apply
    (select cast('<r>'+replace(PName, ',', '</r><r>')+'</r>' as xml)) as x(x)
  cross apply
    x.nodes('r') as r(r)

这篇关于如何在 T-SQL 中拆分由逗号分隔的重复字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！