What is the most optimal way of concatenating string values from column to single string? (T-SQL)

Question

Say I have a table

MemberMoto ( MemberID int, Moto nvarchar(100) )

I need to build a proc that will return coma separated list of Moto values per user. I am looking for a way to accomplish this without using CURSOR/FETCH for performance reasons

Thank you!

Answer 1

One way to do this is by creating your own aggregate function. See http://technet.microsoft.com/en-us/library/ms131056.aspx

Answer 2

I've used the following with great success, this gets a single user's information

DECLARE @results VARCHAR(MAX)
SELECT  @Results = COALESCE(@Results+ ', ' + Moto, Moto)
FROM    MemmberMoto
WHERE MemberId = 1
ORDER BY Moto

Now, you could then extrapolate this out to a function if you needed to call this once per user or something of that nature.

Answer 3

create table MemberMoto (MemberID int, Moto nvarchar(100));

insert into MemberMoto values (1, 'joey');
insert into MemberMoto values (1, 'sally');
insert into MemberMoto values (2, 'yaochun');
insert into MemberMoto values (2, 'willy');

SELECT
   t1.MemberID,
   MotoList = substring((SELECT ( ', ' + Moto )
                           FROM MemberMoto t2
                           WHERE t1.MemberID = t2.MemberID
                           ORDER BY 
                              MemberID
                           FOR XML PATH( '' )
                          ), 3, 1000 )
FROM MemberMoto t1
GROUP BY MemberID

Results:

MemberID MotoList
1        joey, sally
2        yaochun, willy