Q: Distinct comma seperated value for one column in SQL 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?

A: 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;


A: 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
