SQL SERVER: Creating Comma Separate Values List from Table Column

There are two ways to generate a comma separated list from a table column. First is to manually concatenate the result and the second that is more sophisticated is to use the built-in function COALESCE of SQL Server.

Example Problem:
For example you have a table named COUNTRY that has a column named NAME, and you want to generate a comma separated list for countries.

Solution 1:
This is the simple but not the smartest way to implement it:

DECLARE @listStr VARCHAR(MAX)
SET @listStr = ''
SELECT @listStr = @listStr + ISNULL(NAME,'') + ','
FROM COUNTRIES
SELECT SUBSTRING(@listStr , 1, LEN(@listStr)-1)

Solution 2:
This is the smartest way:

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' , '') + NAME
FROM COUNTRIES
SELECT @listStr