There are a number of really detailed posts out there describing an easy way to concatenate the values from a specific column into a delimited list without resorting to a cursor. Using coalesce and a temporary variable, the activity is child’s play:
Assuming you have a table named MyTable with a column named FirstName:
DECLARE @List varchar(MAX)
@List = COALESCE(@List + ', ', '') + CAST (FirstName as varchar (200))
the above will return you something like “Ben, James, Ed”.
Nice and easy, right? But how do you use this in a non-trivial situation where you need to include additional columns in the select list? You can’t do this in the above statement (as far as I’m aware); in my case today, I wanted to a) avoid declaring the temporary variable and b) join the results to another table by Id.
for xml path ('') will help in the first case:
select cast(FirstName as varchar (200)) + ', ' from MyTable
for xml path ('')
but you’ll still get stuck trying to join the results.
Thankfully Arnie Rowland has a really concise SQL wiki post describing how accomplish this feat in SQL Server 2000 and 2005/2008.
Assuming this table:
CREATE TABLE TeamInfo
( MemberID int IDENTITY,
Arnie’s query looks like this:
MemberList = substring((SELECT ', ' + FirstName
FROM TeamInfo t2
WHERE t1.TeamID = t2.TeamID
FOR XML PATH('')
), 3, 1000 )FROM TeamInfo t1
GROUP BY TeamID
And returns this result:
1 Bob, Jim, Mary
2 Ellen, Ralph, Sue
3 Bill, Linda
Note the substring function drops the leading delimiter and limits the result to just under 1000 characters. Also TeamInfo doesn’t have to be the same table, it can be any table with same IDs used in the t2 query.