Looping through a data set and combining values

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

USE [tempdb]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table1]') AND type in (N'U'))
DROP TABLE [dbo].[Table1]
GO

SELECT TOP 1000000 ---- NOTE 1 MILLION rows .
EmailAddress = CAST( ABS( CHECKSUM( NEWID( )) % 90000 ) + 1 AS int ) ,
Msg = CHAR( ABS( CHECKSUM( NEWID( ))) % 26 + 65 )
+ CHAR( ABS( CHECKSUM( NEWID( ))) % 26 + 65 )
+ CHAR( ABS( CHECKSUM( NEWID( ))) % 26 + 65 )
INTO Table1
FROM
sys.all_columns AS ac1
CROSS JOIN sys.all_columns AS ac2
CROSS JOIN sys.all_columns AS ac3;

CREATE CLUSTERED INDEX CIX_EmailAdd ON dbo.Table1( EmailAddress ASC );

SELECT e.EmailAddress AS Email ,
STUFF((
SELECT ',' + t.Msg
FROM Table1 AS t
WHERE t.EmailAddress = e.EmailAddress
FOR XML PATH( '' )) , 1 , 1 , '' )AS MessageBody
FROM Table1 AS e
GROUP BY e.EmailAddress;

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.