CODEWITHSUNDEEP
sqlsql-servert-sqlstring-aggregationstuff
MRR
MRR

Reputation: 83

Concatenate SQL columns with comma separated

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

enter image description here

Upvotes: 1

Views: 18545

Answers (2)

Rajat
Rajat

Reputation: 5803

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;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271023

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, '')

Upvotes: 5

Related Questions