How to split repeating string delimated by commas in T-SQL

Question

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

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

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

Using split function from 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.

Answer 1

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)