SQLTeam.com | Weblogs | Forums

Convert multiple columns into a single column separated by comma

Transact-SQL
#1

Table employee

Id, name,Mon,tue,wed,thu,fri,sat,sun
10,John,1,0,1,1,1,0,0

Expected output

ID,name,week. —-days of week all in one column
10,John,1,0,1,1,1,0,0

#2

select
Id
, name
, Mon+','+Tue+','+Wed+','+Thu+','+Fri+','+Sat+','+Sun
from
employee

#3

If you are on 2017 or later you can use CONCAT_WS:

Select Id
     , name
     , concat_ws(',', Mon, Tue, Wed, Thu, Fri, Sat, Sun)
  From employee
#4

I’m using sql server 2014 concat_ws doesn’t work in the edition. The regular concat works so I’ll use that

#5

Okay - you could then use CONCAT:

Select Id
     , name
     , concat(Mon, ',', Tue, ',', Wed, ',', Thu, ',', Fri, ',', Sat, ',', Sun)
  From employee

The advantage of using CONCAT is that it handles NULL values for you...if the value is NULL it will be converted to an empty string.

1 Like