Combining Columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Combining Columns

Author

Topic

flederflick
Starting Member

2 Posts

Posted - 2008-06-19 : 03:21:19

Hello everybody,

I have the following problem.

I have a database containing about 300 million record made out of 4 years worth of Exchange Logfiles.

I would like to make a query that retreives al send emails, its recipients and message_size.

SELECT msgid, sender_address, recipient_address, number_recipients, total_bytes FROM tbllogfiles

Results:
MSGID, SA , RA , NR , TB
5566 me@domain.nl 1@domain.nl 3 55
5566 me@domain.nl 2@domain.nl 3 55
5566 me@domain.nl 3@domain.nl 3 55

But i'd like to get;

MSGID, SA , RA , NR , TB
5566 me@domain.nl 1@domain.nl,3@domain.nl,2@domain.nl 3 55

Can anyone help me with this query?

Best Regards,

Sidney

raky
Aged Yak Warrior

767 Posts

Posted - 2008-06-19 : 03:44:08

quote:
Originally posted by flederflick

Hello everybody,

I have the following problem.

I have a database containing about 300 million record made out of 4 years worth of Exchange Logfiles.

I would like to make a query that retreives al send emails, its recipients and message_size.

SELECT msgid, sender_address, recipient_address, number_recipients, total_bytes FROM tbllogfiles

Results:
MSGID, SA , RA , NR , TB
5566 me@domain.nl 1@domain.nl 3 55
5566 me@domain.nl 2@domain.nl 3 55
5566 me@domain.nl 3@domain.nl 3 55

But i'd like to get;

MSGID, SA , RA , NR , TB
5566 me@domain.nl 1@domain.nl,3@domain.nl,2@domain.nl 3 55

Can anyone help me with this query?

Best Regards,

Sidney

is this is what you want

declare @test table ( MSGID int, SA nvarchar(500), RA nvarchar(500), NR int , TB int)
insert into @test
select 5566,'me@domain.nl','1@domain.nl', 3,55 union all
select 5566,'me@domain.nl','2@domain.nl', 3,55 union all
select 5566,'me@domain.nl','3@domain.nl', 3,55

select distinct msgid,sa,stuff((select ','+ra from @test t where t.msgid = t1.msgid and t.sa=t1.sa for xml path('')),1,1,'') ra,nr,tb
from @test t1

check this

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-19 : 05:03:05

[code]SELECT s.msgid AS MSGID,
s.sender_address AS SA,
STUFF(g.a, 1, 1, '') AS RA
s.number_recipients AS NR,
s.total_bytes AS TB
FROM (
SELECT DISTINCT
msgid,
sender_address,
number_recipients,
total_bytes
FROM tbllogfiles
) AS s
CROSS APPLY (
SELECT ',' + e.recipient_address
FROM tbllogfiles AS e
WHERE e.msgid = s.msgid
FOR XML PATH('')
) AS g(a)[/code]

E 12°55'05.25"
N 56°04'39.16"

flederflick
Starting Member

2 Posts

Posted - 2008-06-20 : 02:18:07

@Peso:

Thanks. I'm going to try this today.
I'll let you know if it is what i'm looking for.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources