SQL Server concatenation separated by comma only when column is not null

Question

I have the following SQL statement:

@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.

For example:

• if @Asc1 = 1234 and @Asc2 = 3456 and @Asc3 = '', then @All will end up being 1234,3456,

I would like @All to be 1234,3456

Thanks.

Answer 1

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

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

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

returns: 1234,3456