Create comma delimited list in T-SQL (XML PATH)

Create A Comma Delimited List From a Column





SQL Server 2005 / 2008:

--Retrieve desired data
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

-- Results
TeamID     MemberList
1 Bob, Jim, Mary
2 Ellen, Ralph, Sue
3 Bill, Linda

----

SQL Server 2000:

-- SQL 2000, Retrieve desired data
-- With SQL 2000, we will create a User Defined Function to do the concatenation.
-- While this solution can also be used with SQL Server 2005/SQL Server 2008,
-- the previous suggestion is more efficient.

CREATE FUNCTION dbo.fnMakeTeamList
   (  @TeamID int  )
   RETURNS varchar(1000)
AS
   BEGIN
      DECLARE @TempTeam table
         (  Firstname varchar(20)  )
      DECLARE @TeamList varchar(1000)
      SET @TeamList = ''
      INSERT INTO @TempTeam
         SELECT FirstName
         FROM TeamInfo
         WHERE TeamID = @TeamID
      IF @@ROWCOUNT > 0
         UPDATE @TempTeam
            SET @TeamList = ( @TeamList + FirstName + ', ' )
      RETURN substring( @TeamList, 1, ( len( @TeamList ) - 1 ))
   END

-- Usage
SELECT
   TeamID,
   MemberList = dbo.fnMakeTeamList( TeamId )
FROM TeamInfo
GROUP BY TeamID

-- Results
TeamID     MemberList
1 Jim, Mary, Bob
2 Sue, Ralph, Ellen
3 Bill, Linda