如何在COALESCE中获取不同的值（）

I have table values in this format

我有这种格式的表值

sam
jack
sam
john

Declare @name varchar(max)
select @name = COALESCE(@name + ', ','')+ user_email 
from   PostedCommentMaster where article_id = @id

How can I get distinct value

我怎样才能获得不同的价值

sam,jack,john

like this.

喜欢这个。

3 个解决方案

#1

You can wrap the select statement into a subselect and apply coalesce on the results.

您可以将select语句包装到子选择中并对结果应用合并。

Declare @name varchar(max) 

select @name = COALESCE(@name + ', ','') + user_email 
from   (select distinct user_email 
        from   PostedCommentMaster 
        where article_id = @id) pc

Note that this uses an undocumented feature of SQL Server to concatenate the results into one string. While I can't find a link to it anymore, I recall reading that your should not rely on this behavior.

请注意，这使用SQL Server的未记录功能将结果连接成一个字符串。虽然我找不到它的链接，但我记得读到你不应该依赖这种行为。

A better alternative would be to use the FOR XML syntax to return a concatenated string. A search on SO returns multiple results you can use as an example.

更好的选择是使用FOR XML语法返回连接字符串。对SO的搜索会返回您可以用作示例的多个结果。

#2

Here you go

干得好

Declare @name varchar(max) 
select 
    @name = COALESCE(@name + ', ','')+name from (select distinct user_email 
from 
    PostedCommentMaster) as t
where 
    article_id = @id

#3

You can use group by for unique values.

您可以将group by用于唯一值。

Declare @name varchar(max)
select @name = COALESCE(@name + ', ','')+ user_email 
from   PostedCommentMaster where article_id = @id 
group by user_email

#1