Concatenate SQL columns with comma separated

Question

Is there any alternate way to concatenate SQL columns with comma separated. I am using below logic for concatenation. The columns (col1,col2,col3) can have null values.

select 
stuff(
        left(concat(col1,',',col2,',',col3),
            len(concat(col1,',',col2,',',col3)) -
        patindex('%[^,]%',reverse(concat(col1,',',col2,',',col3)))+1
            )
        ,1,
        patindex('%[^,]%',concat(col1,',',col2,',',col3))-1,''
    )
from mytable

Sample data/output

Please provide sample data, desired results, and a tag for the database you are using. — Gordon Linoff, Jun 21 '21 at 11:09
Seems like you'd do MUCH better letting the client code handling this, rather than doing this work on a server where the licensing costs are often as much as $7K/core. — Joel Coehoorn, Jun 21 '21 at 15:22

score 5 · Answer 1 · answered Jun 21 '21 at 11:10

5

In more recent versions of SQL Server you can use concat_ws():

select concat_ws(',', col1, col2, col3)

In earlier versions, there are various approach. A pretty simple one is:

select stuff( concat(',' + col1, ',' + col2, ',' + col3), 1, 1, '')

answered Jun 21 '21 at 11:10

Gordon Linoff

1,242,037
58
646
786

concat_ws - I am using SQL server 2014, so it wont work. – MRR Jun 21 '21 at 13:12
select stuff( concat(',' + col1, ',' + col2, ',' + col3), 1, 1, '') - This is good, works for column having NULL's. what if the data is empty string? – MRR Jun 21 '21 at 13:13
@MRR . . . Then you will get two commas in a row. – Gordon Linoff Jun 21 '21 at 17:32
@MRR, if you have any idea how to concatenate removing the trailing spaces of individual columns – rinilnath Mar 16 '23 at 09:18

Radagast · Accepted Answer · 2021-06-21T16:41:10.723

You can concat separators conditionally. This will output an empty string if either of the columns are null or empty.

select concat(col1,
              case when len(col2)>1 then ',' else '' end,
              col2,
              case when len(col3)>1 then ',' else '' end,
              col3) 
from your_table;

To output null if either of the columns are null or empty, wrap the concat inside a nullif like this

select nullif(concat(col1,
              case when len(col2)>1 then ',' else '' end,
              col2,
              case when len(col3)>1 then ',' else '' end,
              col3),'')
from your_table;

Concatenate SQL columns with comma separated

2 Answers2