Distinct comma seperated value for one column in SQL

Question

I have an query in SQL returns the following result:

Id language
1. English, French, spanish
2. English, English, spanish
3. French, French, English

I don't want to have duplicate value for languages, so if I have two english languages I need to display only one, so the result should be like this:

Id language
1. English, French, spanish
2. English, spanish
3. French, English

How can I do that in SQL?

Can yoiu show us the table structure and the query that returns the result? — Luc, Sep 19 '15 at 17:11
1st mistake -comma separated value.2nd mistake- when value get updated ,that moment you should put validation.to avoid duplicate value.3rd mistake data redundancy i.e. db design is wrong. — KumarHarsh, Sep 19 '15 at 18:12

wiretext · Accepted Answer · 2015-09-20T08:30:45.483

5

XQuery

declare @temp table (Id int,languag nvarchar(33))

insert into @temp values (1,'English, French, spanish')
insert into @temp values (2,'English, English, spanish')
insert into @temp values (3,'French, French, English')

select Id,
cast(cast('<d>'+replace(languag, ', ',',</d><d>')+'</d>'  as xml).query('distinct-values(/d)') as varchar) AS [language]
from @temp

AFTER COMMENT: if i added this row : insert into @temp values (4,'English, English, English') the query returns to me "English, English"

select Id,
REPLACE(cast(cast('<d>'+ replace(languag, ', ','</d><d>')+'</d>'  as xml)
.query('distinct-values(/d)') as varchar), ' ', ', ')AS [language]
from @temp;

edited Sep 20 '15 at 08:30

answered Sep 19 '15 at 17:16

wiretext

3,302
14
19

1

thanks @lad2025 you have been rocking SO from last month :) :P – wiretext Sep 19 '15 at 17:45
There always several ways to sin a cat, however tinka's solution is elegant in that it it's almost a single liner. Anything else would almost certainly need to be at least two statements, one to parse out a comma separated string and the other to reparse it accordingly. Ala http://stackoverflow.com/questions/8566371/find-unique-values-in-a-column-of-comma-separated-text – Rachel Ambler Sep 19 '15 at 22:06
Thanks, if i added this row : `insert into @temp values (4,'English, English, English')` the query returns to me **"English, English"** i need only one. and one more thing is there any way to order these values before getting the distinct ? – Ayman Barhoum Sep 20 '15 at 04:39

score 1 · Answer 2 · answered Sep 20 '15 at 07:51

Now it is working fine with sorting and getting distinct values:

declare @temp table (Id int,languag nvarchar(33))

insert into @temp values (1,'English, French, spanish')
insert into @temp values (2,'English, English, spanish')
insert into @temp values (3,'French, French, English')
insert into @temp values (4,'English, English, English')

select Id,
REPLACE( cast(cast('<d>'+ replace(languag, ', ','</d><d>')+'</d>'  as xml)
.query('for $a in distinct-values(/d) order by $a return $a') as varchar), ' ', ', ')AS [language]
from @temp

Thanks @tinka

Distinct comma seperated value for one column in SQL

2 Answers2