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?
very quick proof of concept...may be this will help you...takes sub three seconds to return c90k rows...and I have a poorly pc 😉
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;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day