0 COALESCE

Table : People

Name

Peter
Paul
Mary

DECLARE @Names VARCHAR(8000) SELECT @Names = COALESCE(@Names + ', ', '') + Name FROM People

Just some explanation (since this answer seems to get relatively regular views):

• Coalesce is really just a helpful cheat that accomplishes two things: 1) No need to initialize @Names with an empty string value. 2) No need to strip off an extra separator at the end.
• The solution above will give incorrect results if a row has a NULL Name value (if there is a NULL, the NULL will make @Names NULL after that row, and the next row will start over as an empty string again. Easily fixed with one of two solutions:

DECLARE @Names VARCHAR(8000) SELECT @Names = COALESCE(@Names + ', ', '') + NameFROM PeopleWHERE Name IS NOT NULL

or:

DECLARE @Names VARCHAR(8000) SELECT @Names = COALESCE(@Names + ', ', '') + ISNULL(Name, 'N/A')
FROM People

Depending on what behavior you want (the first option just filters NULLs out, the second option keeps them in the list with a marker message [replace 'N/A' with whatever is appropriate for you]).

One method not yet shown via the XML data() command in MS SQL Server is:

Assume table called NameList with one column called FName,

select FName + ', ' as 'data()' from NameList for xml path('')