InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 04:48:09 GMT20http://www.sqlservercentral.com/Forums/Topic1366798-338-1.aspx[quote][b]oscarooko (10/5/2012)[/b][hr]Just discovered a new problem, and I cant get round it. The data is now getting XML encoded < to &lt and > to &gt etcAny ideas?[/quote]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:[code="sql"]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 MessageBodyfrom @Table1 egroup by e.EmailAddress [/code]The change is this line: [code="sql"]for xml path(''),root('a'),type).value('(a/text())[1]','varchar(4000)'),1,1,'') as MessageBody[/code]Where I added [code="sql"],root('a'),type[/code] to the FOR XML PATH('')and[code="sql"].value('(a/text())[1]','varchar(4000)')[/code] to pull the text out again...Fri, 05 Oct 2012 16:44:40 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1366798-338-1.aspxdo a replace around it. Each character will be XML encoded and you can simply replace it back.Fri, 05 Oct 2012 15:12:25 GMTvenoymhttp://www.sqlservercentral.com/Forums/Topic1366798-338-1.aspxJust discovered a new problem, and I cant get round it. The data is now getting XML encoded < to &lt and > to &gt etcAny ideas?Fri, 05 Oct 2012 14:46:32 GMToscarookohttp://www.sqlservercentral.com/Forums/Topic1366798-338-1.aspx[quote]...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[/quote]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.Tue, 02 Oct 2012 09:54:23 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1366798-338-1.aspxEugene et al...It worked! I appreciate your help.Tue, 02 Oct 2012 09:37:41 GMToscarookohttp://www.sqlservercentral.com/Forums/Topic1366798-338-1.aspx[quote][b]Eugene Elutin (10/2/2012)[/b][hr][quote][b]oscarooko (10/2/2012)[/b][hr][quote][b]Eugene Elutin (10/2/2012)[/b][hr]Use XML FOR PATH method (it most likely will give you the best performance for T-SQL implementation):[code="sql"]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 MessageBodyfrom @Table1 egroup by e.EmailAddress [/code][/quote]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?[/quote]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.[/quote]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 adviceTue, 02 Oct 2012 09:23:06 GMToscarookohttp://www.sqlservercentral.com/Forums/Topic1366798-338-1.aspx[quote][b]oscarooko (10/2/2012)[/b][hr][quote][b]Eugene Elutin (10/2/2012)[/b][hr]Use XML FOR PATH method (it most likely will give you the best performance for T-SQL implementation):[code="sql"]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 MessageBodyfrom @Table1 egroup by e.EmailAddress [/code][/quote]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?[/quote]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 ;-)[code="sql"]USE [tempdb]GOIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table1]') AND type in (N'U'))DROP TABLE [dbo].[Table1]GOSELECT 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; [/code]Tue, 02 Oct 2012 08:54:00 GMTJ Livingston SQLhttp://www.sqlservercentral.com/Forums/Topic1366798-338-1.aspx[quote][b]oscarooko (10/2/2012)[/b][hr][quote][b]Eugene Elutin (10/2/2012)[/b][hr]Use XML FOR PATH method (it most likely will give you the best performance for T-SQL implementation):[code="sql"]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 MessageBodyfrom @Table1 egroup by e.EmailAddress [/code][/quote]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?[/quote]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.Tue, 02 Oct 2012 08:18:33 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1366798-338-1.aspx[quote][b]Eugene Elutin (10/2/2012)[/b][hr]Use XML FOR PATH method (it most likely will give you the best performance for T-SQL implementation):[code="sql"]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 MessageBodyfrom @Table1 egroup by e.EmailAddress [/code][/quote]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?Tue, 02 Oct 2012 07:10:41 GMToscarookohttp://www.sqlservercentral.com/Forums/Topic1366798-338-1.aspxUse XML FOR PATH method (it most likely will give you the best performance for T-SQL implementation):[code="sql"]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 MessageBodyfrom @Table1 egroup by e.EmailAddress [/code]Tue, 02 Oct 2012 03:59:30 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1366798-338-1.aspx[quote][b]Ray M (10/1/2012)[/b][hr]A good read[url=http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/]concatenating-row-values-in-transact-sql[/url][/quote]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.Mon, 01 Oct 2012 16:00:58 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1366798-338-1.aspxA good read[url=http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/]concatenating-row-values-in-transact-sql[/url]Mon, 01 Oct 2012 15:44:03 GMTRay Mhttp://www.sqlservercentral.com/Forums/Topic1366798-338-1.aspxI have the following table[b]Table1[/b]EmailAdress Msgemail1 blue email2 blackemail1 whiteemail1 orangeemail4 red[b]Table2[/b]Email Bodyemail1 blue,white,orangeemail2 blackemail4 redMy 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!!Mon, 01 Oct 2012 15:40:48 GMToscarooko