SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Looping through a data set and combining values


Looping through a data set and combining values

Author
Message
oscarooko
oscarooko
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 153
I have the following table

Table1

EmailAdress Msg
email1 blue
email2 black
email1 white
email1 orange
email4 red


Table2

Email Body
email1 blue,white,orange
email2 black
email4 red


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!!
Ray M
Ray M
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2207 Visits: 1076
A good read
concatenating-row-values-in-transact-sql
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87718 Visits: 41121
Ray M (10/1/2012)
A good read
concatenating-row-values-in-transact-sql


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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5068 Visits: 5478
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
oscarooko
oscarooko
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 153
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
Eugene Elutin
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5068 Visits: 5478
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
J Livingston SQL
J Livingston SQL
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5515 Visits: 35459
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

oscarooko
oscarooko
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 153
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
oscarooko
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 153
Eugene et al...It worked! I appreciate your help.
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5068 Visits: 5478
...
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search