Eugene Elutin (10/2/2012)

---

oscarooko (10/2/2012)

---

Eugene Elutin (10/2/2012)

---

Use XML FOR PATH method (it most likely will give you the best performance for T-SQL implementation):

declare @Table1 table (EmailAddress varchar(20), Msg varchar(20))

insert @Table1

select 'email1','blue'

union select 'email2','black'

union select 'email1','white'

union select 'email1','orange'

union select 'email4','red'

select e.EmailAddress as Email

,stuff((select ',' + t.Msg

from @Table1 t where t.EmailAddress = e.EmailAddress

for xml path('')),1,1,'') as MessageBody

from @Table1 e

group by e.EmailAddress

That looks like it can work, but for only short table with known number of rows. In my case, I don't know how many rows I will be coming up with every time I run the query. Is tehre a way i can tweak it to accommodate indefinite number of rows?

Why do you think it can only work for "short table"? It's work absolutely fine for multi-million rows tables with much more complicated logic.

If you not happy with its performance, you may try creating aggregate CLR function in c#. But, it is not guaranteed that it will outperform the xml method.