Create a Comma Delimited List from a Column in a Table

Hi,

Last week I was working on concatenate column values separated with semi colon in a table.I found a good script to do this example. It is pretty fast when I compared with different examples. Lets see how it looks:
Scenario:


ID TeamID FirstName
1 1         Maruthi
2 1         Siva
3 1         Prasad
4 2         Parviz
5 2         Javadian
6 2         P
7 3         Amit
8 3         Kumar

Output:

TeamID MemberList
1         Maruthi; Prasad; Siva
2         Javadian; P; Parviz
3         Amit; Kumar

Implementation:

-- Create a table with Sample Data 
CREATE TABLE CommaSeparated
   (  ID    int IDENTITY,
      TeamID      int, 
      FirstName   varchar(50)
   )

-- Load Sample Data
INSERT INTO CommaSeparated VALUES ( 1, 'Maruthi' )
INSERT INTO CommaSeparated VALUES ( 1, 'Siva' )
INSERT INTO CommaSeparated VALUES ( 1, 'Prasad' )
INSERT INTO CommaSeparated VALUES ( 2, 'Parviz' )
INSERT INTO CommaSeparated VALUES ( 2, 'Javadian' )
INSERT INTO CommaSeparated VALUES ( 2, 'P' )
INSERT INTO CommaSeparated VALUES ( 3, 'Amit' )
INSERT INTO CommaSeparated VALUES ( 3, 'Kumar' )

Select * from CommaSeparated

--Retrieve data with semicolon 
SELECT
   t1.TeamID,
   MemberList = substring((SELECT ( DISTINCT '; ' + FirstName )
                           FROM CommaSeparated t2
                           WHERE t1.TeamID = t2.TeamID
                           ORDER BY 
                              TeamID,
                              FirstName
                           FOR XML PATH( '' )
                          ), 3, 1000 )FROM CommaSeparated t1
GROUP BY TeamID

-----------------------------------------------------------------------------------
Another way to implement the same logic:
-- But the below query will be very slow if you have huge data in table.


with FinalCommaSeparated as
(
select *, ROW_NUMBER() over (partition by TeamID order by FirstName) rownum
from CommaSeparated
) 
select distinct TeamID,(
select FirstName 
+ case when s1.rownum = (select MAX(rownum) from FinalCommaSeparated where TeamID = s1.TeamID)  
then '' else '; ' end from FinalCommaSeparated s1
where s1.TeamID = s2.TeamID
for xml path(''),type).value('(.)[1]','Nvarchar(max)') MemberList

from FinalCommaSeparated s2
------------------------------------------------------------------------------------
Added one more way to do the same by using STUFF function in SQL:


SELECT DISTINCT
   t1.TeamID,
   MemberList = STUFF((SELECT ( DISTINCT '; ' + FirstName )
                           FROM CommaSeparated t2
                           WHERE t1.TeamID = t2.TeamID
                           FOR XML PATH( '' )
                          ), 1, 1,'' )FROM CommaSeparated t1
GROUP BY TeamID