This can also be represented as:
DECLARE @strList varchar(max)= ''
SELECT @strList += COALESCE(CAST(Name as varchar(5) + ', ', '') FROM TABLE1
SELECT LEFT(@strList, LEN(@strList) - 1)
A few problems with your approach: If the rows add up to more than 100 characters, you'll get either truncation or an overflow error. If any of them contain NULL, the resulting string will be NULL. In your example, there would also be a trailing comma.
commented on Jan 15 2012 11:33AM