Cacher is the code snippet organizer for pro developers

Use COALESCE:

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 + ', ', '') + Name
FROM People
WHERE 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])