当列不是null时，SQL Server用逗号分隔的连接

I have the following SQL statement:

我有以下SQL语句:

@All = COALESCE(NULLIF(@Asc1, '') + ',', '') +
        OALESCE(NULLIF(@Asc2, '') + ',', '') +
        OALESCE(NULLIF(@Asc3, '') + ',', '');

This will insert a comma at the end even if any of the variables (@Asc1, @Asc2, @Asc3) have NULL or empty values.

这将在末尾插入一个逗号，即使有任何变量(@Asc1， @Asc2， @Asc3)都有NULL或空值。

For example:

例如:

if @Asc1 = 1234 and @Asc2 = 3456 and @Asc3 = '', then @All will end up being 1234,3456,
如果@Asc1 = 1234， @Asc2 = 3456， @Asc3 = "，则@All最终会是1234,3456，

I would like @All to be 1234,3456

我希望@All是1234 3456

Thanks.

谢谢。

2 个解决方案

#1

using stuff() to remove the first comma and reversing the comma concatenation:

使用stuff()删除第一个逗号，反转逗号连接:

set @all = stuff(
    coalesce(','+nullif(@Asc1, ''), '')
  + coalesce(','+nullif(@Asc2, ''), '')
  + coalesce(','+nullif(@Asc3, ''), '')
  ,1,1,'');

rextester demo: http://rextester.com/UNDS90887

rextester演示:http://rextester.com/UNDS90887

returns: 1234,3456

返回:1234、3456

#2

I know this post is already answered and is correct. But would like to post the below answer because from SQL Server 2017 onwards, this is too easy and someoen might find this helpful in future.

我知道这个帖子已经被回复了，是正确的。但是我想发布下面的答案，因为从SQL Server 2017年开始，这太容易了，有些人可能会发现这对将来很有帮助。

SELECT @all = CONCAT_WS(',',@Asc1,@Asc2,@Asc3)

#1