Looping through a data set and combining values

Question

Post reply

Looping through a data set and combining values

oscarooko

SSC-Addicted

Points: 445
More actions
October 1, 2012 at 3:40 pm

#282380

I have the following table
Table1
EmailAdress Msg
email1 blue
email2 black
email1 white
email1 orange
email4 red
Table2
EmailBody
email1blue,white,orange
email2black
email4red
My goal is to go though the messages in table1 and join them to make one message for each email in table two. For example, for email1, the corresponding value in "Body" column should be blue,white,orange
It doesn matter what is used to separate values ie, comma, space, semi colon etc...
Please help!!
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Ray M SSC-Insane Points: 21093 More actions · Answer 1

A good read

concatenating-row-values-in-transact-sql[/url]

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

Ray M (10/1/2012)
A good read
concatenating-row-values-in-transact-sql[/url]

The problem I have with that particular article is that there's no performance testing. Someone might take a liking to the Scalar UDF or recursive versions without any investigation at all.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

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

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

oscarooko SSC-Addicted Points: 445 More actions · Answer 4

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?

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

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.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 6

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 )

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

oscarooko SSC-Addicted Points: 445 More actions · Answer 7

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.

I really appreciate your help. My reasoning was that do I have to repeat all the "Select Union" statements for all the rows that i have?

I could be missing something! Please advice

oscarooko SSC-Addicted Points: 445 More actions · Answer 8

oscarooko

SSC-Addicted

Points: 445

October 2, 2012 at 9:37 am

#1544459

Eugene et al...It worked! I appreciate your help.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 9

...
I really appreciate your help. My reasoning was that do I have to repeat all the "Select Union" statements for all the rows that i have?
I could be missing something! Please advice

Ough! Just that? That was used to create and populate a sample table and data for demonstration. I expect you have your table in place already with data populated...

😉

If you want to populate another table, just add INSERT INTO [RequiredTable] before SELECT statement which does the work.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

oscarooko SSC-Addicted Points: 445 More actions · Answer 10

Just discovered a new problem, and I cant get round it. The data is now getting XML encoded < to &lt and > to &gt etc

Any ideas?

venoym SSCarpal Tunnel Points: 4161 More actions · Answer 11

do a replace around it. Each character will be XML encoded and you can simply replace it back.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 12

oscarooko (10/5/2012)
Just discovered a new problem, and I cant get round it. The data is now getting XML encoded < to &lt and > to &gt etc
Any ideas?

You need to apply a root node to the XML, force it into XML datatype and then select out the text from the root node....like this:

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(''),root('a'),type).value('(a/text())[1]','varchar(4000)'),1,1,'') as MessageBody

from @Table1 e

group by e.EmailAddress

The change is this line:

for xml path(''),root('a'),type).value('(a/text())[1]','varchar(4000)'),1,1,'') as MessageBody

Where I added

,root('a'),type to the FOR XML PATH('')

and

.value('(a/text())[1]','varchar(4000)') to pull the text out again...

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);