From what I can see FOR XML
(as posted earlier) is the only way to do it if you want to also select other columns (which I'd guess most would) as the OP does.
Using COALESCE(@var...
does not allow inclusion of other columns.
Update:
Thanks to programmingsolutions.net there is a way to remove the "trailing" comma to.
By making it into a leading comma and using the STUFF
function of MSSQL you can replace the first character (leading comma) with an empty string as below:
stuff(
(select ',' + Column
from Table
inner where inner.Id = outer.Id
for xml path('')
), 1,1,'') as Values