This is a little trick that I figured out a long time ago, as in like 4 years ago. But a coworker recently applied the trick because he had seen some of my old code and he needed to do the same thing.
This works well inside stored procedures and user-defined functions, but it cannot be used in a view or inline query.
1: DECLARE @Names TABLE (Name varchar(20))
2: INSERT @Names
3: SELECT 'Jeff' UNION ALL
4: SELECT 'Mark' UNION ALL
5: SELECT 'Chris'
6:
7: DECLARE @NameList varchar(255)
8: SELECT @NameList = IsNull(@NameList + ', ', '') + Name
9: FROM @Names
10: ORDER BY Name ASC
11:
12: SELECT @NameList
The output is "Chris, Jeff, Mark."