Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Looping through a data set and combining values Expand / Collapse
Author
Message
Posted Monday, October 1, 2012 3:40 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 3:52 PM
Points: 27, 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!!
Post #1366798
Posted Monday, October 1, 2012 3:44 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, October 24, 2014 9:55 AM
Points: 1,485, Visits: 1,036
A good read
concatenating-row-values-in-transact-sql
Post #1366801
Posted Monday, October 1, 2012 4:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:32 PM
Points: 35,584, Visits: 32,174
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1366811
Posted Tuesday, October 2, 2012 3:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:23 AM
Points: 2,873, Visits: 5,189
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1366930
Posted Tuesday, October 2, 2012 7:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 3:52 PM
Points: 27, 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?
Post #1367015
Posted Tuesday, October 2, 2012 8:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:23 AM
Points: 2,873, Visits: 5,189
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1367067
Posted Tuesday, October 2, 2012 8:54 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:55 AM
Points: 1,935, Visits: 19,982
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
Post #1367094
Posted Tuesday, October 2, 2012 9:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 3:52 PM
Points: 27, 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
Post #1367115
Posted Tuesday, October 2, 2012 9:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 3:52 PM
Points: 27, Visits: 153
Eugene et al...It worked! I appreciate your help.
Post #1367128
Posted Tuesday, October 2, 2012 9:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:23 AM
Points: 2,873, Visits: 5,189
...
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1367138
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse