How to Select Column Values as Comma Separated String in Sql Server

How to Select Column Values as Comma Separated String in Sql Server

Sometimes we required to generate a comma separated list of columns values like a list of EmailIDs to send mail. In SQL Server, we can make a comma separated list by using COALESCE as shown in below.

Use of COALESCE to create comma separated list

Suppose we have following data in Employee table and we need to make a semicolon separated list of

EmailIDs to send mail, then we can use COALESCE as shown in below fig.

Here I am creating a semicolon(;) separated list. You can use comma(,) in place of semicolon to make comma separated list.

Declare @strEmails varchar(max)

select @strEmails=coalesce(@strEmails+';','')+EmailId from tblEmployee

print @strEmails