Selecting a delimited list of rows with other values in SQL

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)

SELECT 
        @List = COALESCE(@List + ', ', '') + CAST (FirstName as varchar (200))
FROM
        MyTable

select @List

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,
      TeamID      int, 
      FirstName   varchar(50)
   )

Arnie’s query looks like this:

SELECT
   t1.TeamID,
   MemberList = substring((SELECT ', ' + FirstName
                           FROM TeamInfo t2
                           WHERE t1.TeamID = t2.TeamID
                           ORDER BY 
                              TeamID,
                              FirstName
                           FOR XML PATH('')
                          ), 3, 1000 )FROM TeamInfo t1
GROUP BY TeamID

And returns this result:

TeamID     MemberList
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.