問題描述

我已經嘗試了一些試驗，但仍然無法弄清楚.任何幫助將不勝感激.

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 中拆分由逗號分隔的重復字符串的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！