如何在 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)