Select column values as comma separated or delimited 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 some methods as shown in below-

Suppose we have some data in 'tblUsers' table as shown in below-

Now, I am creating a semicolon(;) separated list. You can also use comma(,) or other symbol in place of semicolon.

1. Use of COALESCE inbuilt function

DECLARE @allUserEmails NVARCHAR(MAX)
SELECT @allUserEmails = COALESCE(@allUserEmails + '; ', '')+ userEmail from tblUsers
SELECT @allUserEmails allUserEmails


2. Use of STUFF inbuilt function

SELECT STUFF((
SELECT '; ' + userEmail FROM tblUsers
FOR XML PATH('')
),1, 1, '') allUserEmails


Output: