InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 20:29:51 GMT20http://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspxsolution 3select LEFT(concatenations,len(concatenations)-1) from (select (select cast(name as varchar)+','from fruit order by id for xml path('')) as concatenations)aMon, 15 Aug 2011 23:39:44 GMTlilian.buhttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspxThanks for the great post. It almost completely solved an issue I had at work. I only needed the concatenation to work [i]by group[/i] within the result set. I posted my code on my blog here: [url=http://geekswithblogs.net/TomPepe/archive/2011/07/27/using-cte-to-concatenate-rows-by-group.aspx][/url]Thu, 28 Jul 2011 08:39:05 GMTpepe 24347http://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspxHow about something like this: (similar to solution 2)DECLARE @listCol VARCHAR(2000)SELECT @listCol = STUFF(( SELECT DISTINCT ', ' + name FROM fruit ORDER BY 1 FOR XML PATH('') ), 1, 2, ' ') select @listcolTue, 08 Mar 2011 13:10:53 GMTchasbabbhttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspx[quote][b]jim Etheridge (3/7/2011)[/b][hr]This works to simplify the 'for xml path ('')' option: {snip]The rtrim/ltrim function apparently removes the unprintable xml tokens, and the substring removes the first comma. This works consistently, where used without the rtrim/ltrim is spotty, as the author suggests.[/quote]Thanks, Jim. :-) Cool tip. I'm going to have to try it.Mon, 07 Mar 2011 17:04:08 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspx[quote][b]SQLkiwi (3/7/2011)[/b][hr][quote][b]Jeff Moden (3/7/2011)[/b][hr]At the risk of quibbling, yeah... it's RBAR because it requires a scalar UDF (or equivalent) to return just one text aggregate and it handles the same data over and over until the full string is constructed. ;-) Call it what you want, though, because no matter what you and I call it, we both agree that it's also called "fairly slow". :-D[/quote]I think I see what you mean, but we're talking about the "SELECT @fruit = COALESCE(@fruit + '', '') + name FROM table" method aren't we? There's no UDF or explicit loop there...or am I missing something? Quite possible, it is 4:40am![/quote]True enough. If you only need to create a single text aggregate, you don't need a scalar UDF. It's still RBAR, though. Explicit loops aren't the only form of RBAR. There is such a thing as "Hidden RBAR". You remember the "Triangular Join" thing for sums that you and I had fun with? Even Ben-gan wrote about the O[sup]2[/sup] problem with using COALESCE (or ISNULL) to overwrite variables in such a fashion.My problem with people who use this method even on short stuff is that some unlucky sucker who has a schedule to meet may "leverage" the code for something bigger and against a whole table instead of just one "grouping".Mon, 07 Mar 2011 17:02:42 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspxThis works to simplify the 'for xml path ('')' option:select substring((Select rtrim(ltrim(','+name)) from fruit order by id for xml path('')),2,1000) as FruitListThe rtrim/ltrim function apparently removes the unprintable xml tokens, and the substring removes the first comma. This works consistently, where used without the rtrim/ltrim is spotty, as the author suggests.Mon, 07 Mar 2011 12:12:08 GMTjim Etheridgehttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspx[quote][b]Jeff Moden (3/7/2011)[/b][hr]At the risk of quibbling, yeah... it's RBAR because it requires a scalar UDF (or equivalent) to return just one text aggregate and it handles the same data over and over until the full string is constructed. ;-) Call it what you want, though, because no matter what you and I call it, we both agree that it's also called "fairly slow". :-D[/quote]I think I see what you mean, but we're talking about the "SELECT @fruit = COALESCE(@fruit + '', '') + name FROM table" method aren't we? There's no UDF or explicit loop there...or am I missing something? Quite possible, it is 4:40am!Mon, 07 Mar 2011 08:39:37 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspxNever mind on the .WRITE method. I just found the answer in Flo's article that you provided a link for. Thanks for the link.Mon, 07 Mar 2011 08:34:14 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspx[quote][b]SQLkiwi (3/7/2011)[/b][hrWell it's not really RBAR - it is a set-based solution of sorts, but you are absolutely right to point out that it is not at all suitable for a large number of elements, or an otherwise long result.[/quote]At the risk of quibbling, yeah... it's RBAR because it requires a scalar UDF (or equivalent) to return just one text aggregate and it handles the same data over and over until the full string is constructed. ;-) Call it what you want, though, because no matter what you and I call it, we both agree that it's also called "fairly slow". :-D[quote]Another high-performance solution which has no character restrictions and a more intuitive syntax is based on SQLCLR. For [i]very[/i] large strings, the RBAR .WRITE method is surprisingly worth considering. You can find test scripts and performance comparisons here:[/quote]This DOES seem like one of those places where SQLCLR would certainly outstripe T-SQL prestidigitaion. Have you done a comparison between the two>Another question, please because I don't work with C#... when you speak of the .WRITE method, are you talking about T-SQL or the SQLCLR?Mon, 07 Mar 2011 08:21:22 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspx[quote][b]Jeff Moden (3/5/2011)[/b][hr][quote][b]arturv (3/4/2011)[/b][hr]Hi guys,why dont we keep it simple?SELECT @fruit = COALESCE(@fruit + '', '') + name[/quote]Because that's RBAR an solves only for one scalar return. Also, if the list is long, the code begins to slow down almost exponentially because it has to keep rebuilding the variable internally as well as growing the memory allocation for the variable which is also relatively expensive.[/quote]Well it's not really RBAR - it is a set-based solution of sorts, but you are absolutely right to point out that it is not at all suitable for a large number of elements, or an otherwise long result.There are several other methods which are much better: the FOR XML PATH trick is the one most commonly employed. Yes, it may be a bit of a hack (it's an odd use of 'XML') and the contortions required to handle special characters like & can be annoying, and it won't work at all with some rare embedded characters, but even with all those caveats, it's still generally much to be preferred. Until Microsoft provide a well-performing built-in CONCAT function, it's one of the better options.Another high-performance solution which has no character restrictions and a more intuitive syntax is based on SQLCLR. For [i]very[/i] large strings, the RBAR .WRITE method is surprisingly worth considering. You can find test scripts and performance comparisons here:[url]http://florianreischl.blogspot.com/2010/01/concatenation-of-text-and-binary-data.html[/url]PaulMon, 07 Mar 2011 07:06:45 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspx[quote][b]guruprasat85 (3/7/2011)[/b][hr]Declare @var varchar(1000)Set @var=''Select @var = @var + fruit + ',' from #tempSelect substring(@var,1,len(@var)-1)[/quote]Please read the posts above for why that's a bad idea and what to do about it. ;-)Mon, 07 Mar 2011 06:14:20 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspxDeclare @var varchar(1000)Set @var=''Select @var = @var + fruit + ',' from #tempSelect substring(@var,1,len(@var)-1)Mon, 07 Mar 2011 03:27:59 GMTguruprasat85http://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspx[quote][b]Robert Bourdeau (3/4/2011)[/b][hr]It is a shame that TSQL lacks the CONNECT BY clause that is available with Oracle. All these complex solutions could be eliminated for many classes of row-joining queries.[/quote]Hi Robert,I know this is an SQL Server forum but I sure would like to see the Oracle code that uses CONNECT BY to do concatenation to build CSV's like what they have on this thread. Thanks.Sun, 06 Mar 2011 11:54:28 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspx[quote][b]Jeff Moden (10/18/2009)[/b][hr]And here's a function for items that don't exceed 8K...[code="sql"]--===== Create a function that uses VARCHAR(8000) with a DUPE Eliminator CREATE FUNCTION dbo.Concat8KTestDupElim (@SomeInt INT)RETURNS VARCHAR(8000) AS BEGIN DECLARE @ReturnString VARCHAR(8000) SELECT @ReturnString = ISNULL(@ReturnString+',' ,'') + d.SomeCharValue FROM [font="Arial Black"](SELECT DISTINCT SomeCharValue FROM dbo.SomeTable --Need to hardcode WHERE SomeInt = @SomeInt) d[/font] RETURN @ReturnString ENDGO[/code][/quote]BTW... just so folks know. Even this is RBAR because it's a scalar function. I apologize for even posting it.Sun, 06 Mar 2011 11:51:38 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspx[quote][b]Carlo Romagnano (10/17/2009)[/b][hr][quote][b]msam77-503326 (10/15/2009)[/b][hr]Below is simple sql that does thatdeclare @res varchar(Max)select @res = coalesce(@res + ',', '') + [name] from( select 'Apple' as name, 101 as id union all select 'Banana' as name, 102 as id union all select 'Orange' as name, 103 as id union all select 'Melon' as name, 104 as id union all select 'Grape' as name, 105 as id ) aselect @res[/quote]The best one always is my version without coalesce, replace and without removing final comma:create table fruit(name varchar(20))insert into fruit SELECT 'apple'union all select 'banana'union all select 'orange'union all select 'melon'union all select 'grape'declare @l varchar(8000),@comma varchar(2) set @l = '' set @comma = '' select @l = @l + @comma + name ,@comma = ', ' from fruit ORDER BY nameselect @l[/quote]Try your RBAR method with a million rows of data. You'll change your mind about it being the "best". ;-)Sun, 06 Mar 2011 11:49:37 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspx[quote][b]burtsev (3/4/2011)[/b][hr][quote][b]bnordberg (10/14/2009)[/b][hr]I recently had to concatenate ~1.5 billion rows of text (average length of 110 characters). These were medical notes where each line was a sepearte row. I tested CLR, Stored procedures ... nothing was a fast as the XML method. However I ran into tons of unicode characters that I had to replace. So I had a yucky 18 or so level nested replace. I was able to get the concatenation to work in 2.4 hours creating 34 million documents out of the 1.5 billion. The CLR would have definitly been useful to avoid the unicode character problem, but still I can't get it to beat the xml method.[/quote]Hi, have you tried to extract strings from XML correctly as it described here?[url=http://www.codeproject.com/Tips/122630/String-concatenation-in-Transact-SQL.aspx]http://www.codeproject.com/Tips/122630/String-concatenation-in-Transact-SQL.aspx[/url][/quote]That's not actually a "correct" method for XML because of two things. The first thing is the output is directed to a scalar variable. The second thing is that if you really want to get rid of the "unicode character problem", use the keyword TYPE.Sun, 06 Mar 2011 11:47:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspx[quote][b]kaplan.dani (3/4/2011)[/b][hr]I think this (using a cursor to iterate on the list) is the most reasonable solution for the newbie sql dveloper.Is there a performance issue in this way ?I think it shuold beat the recursion any time, and I'm not sure about the other one (xml path)Any insights ?[/quote]Yes... the XML Path method of concatenating will blow the doors off of Cursors, While Loops, AND Recursive CTE's. My other thought is that Newbie SQL Developers should not be allowed to use any of those looping methods for the first two years of their career. Instead, they should be taught proper set-based methods instead of learning how to use the crutch of RBAR. ;-)Sun, 06 Mar 2011 11:42:53 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspx[quote][b]RHaverty 8478 (3/4/2011)[/b][hr]My solution where I posted the coalesce solution will eat up a cursor on performance. Another better solution than using a cursor is:[/quote]But, it's not. It still contains a WHILE loop which gives no better performance than a read only, forward only, static cursor. It's not the cursor that slows things down. It's the WHILE loop needed to step through the process.Sun, 06 Mar 2011 11:39:30 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspx[quote][b]vkhabas (3/4/2011)[/b][hr]Sorry Guys, as I remember the CTE recursion works with depth <= 70[/quote]Actually, the default for the number of resursions is 100. The max explicit size is a bit over 32,000. However, if you set the MAXRECURSION option to 0, it can run virtually forever.Sun, 06 Mar 2011 11:36:59 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspx[quote][b]a.rajmane (3/6/2011)[/b][hr]Another simple way of doing this ... see the example at [url=http://www.sqlsuperfast.com/post/2011/02/17/T-SQL-to-Convert-rows-into-single-column.aspx]http://www.sqlsuperfast.com/post/2011/02/17/T-SQL-to-Convert-rows-into-single-column.aspx[/url][/quote]You had a bad link in your post. I fixed it in the above quote.Both of those methods have already been covered on this thread. The first method is the RBAR method I've been suggesting that people don't use.Sun, 06 Mar 2011 11:34:07 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspxanother simple way of doing this see at [url=http://www.sqlsuperfast.com/post/2011/02/17/T-SQL-to-Convert-rows-into-single-column.aspx]http://www.sqlsuperfast.com/post/2011/02/17/T-SQL-to-Convert-rows-into-single-column.aspx[/url]Sun, 06 Mar 2011 05:18:34 GMTa.rajmanehttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspxAnother simple way of doing this ... see the example at [url=http://www.sqlsuperfast.com]http://www.sqlsuperfast.com/post/2011/02/17/T-SQL-to-Convert-rows-into-single-column.aspx[/url]Sun, 06 Mar 2011 05:16:00 GMTa.rajmanehttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspx[quote][b]arturv (3/5/2011)[/b][hr]Sometimes the "best practices programming" its not best or more profitable solution.But this is just an opinion[/quote]Then you won't mind me expressing my opinion. ;-) It takes no longer to do things correctly than it does to fall back on RBAR. The key is that you have to be well practiced enough to know the set based method instead of falling back on RBAR. The only way to do that is to practice.Also, NEVER justify the use of RBAR just because of a supposedly small number of rows because you don't actually know what someone else will do with either the tables involved or the code. It'll also make you "weak" because, instead of practicing good set based methods, you keep using RBAR.Sat, 05 Mar 2011 11:37:45 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspxHi Jeff,Thanks for your reply.You're right... this can be used as an example of RBAR.But if we consider that this specific operation is limited to 8000 characters, its a very "small" RBAr:-DIts all comes down to what is pretended, how much it costs to develop, and the time you have, the available resources, etc...Because there are many ways to achieve the same objective, we always show look for alternatives and choose the one that serves us better.Sometimes the "best practices programming" its not best or more profitable solution.But this is just an opinionArtur[i]The answer to all questions about SQL Server is: It depends..., except "Should auto-shrink be enabled"[/i]Sat, 05 Mar 2011 10:20:44 GMTarturvhttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspx[quote][b]arturv (3/4/2011)[/b][hr]Hi guys,why dont we keep it simple?CREATE TABLE #test (id INT NULL, NAME NVARCHAR(20) NULL)INSERT INTO #test ( id, name )VALUES ( 101, 'Apple' ), ( 102, 'Banana' ), ( 103, 'Orange' ), ( 104, 'Melon' ), ( 105, 'Grape' )SELECT * FROM #testDECLARE @fruit VARCHAR(MAX) SELECT @fruit = COALESCE(@fruit + '', '') + nameFROM #test SELECT Colors = @fruitIt take no efford at all.Artur[/quote]Because that's RBAR an solves only for one scalar return. Also, if the list is long, the code begins to slow down almost exponentially because it has to keep rebuilding the variable internally as well as growing the memory allocation for the variable which is also relatively expensive.Sat, 05 Mar 2011 09:53:17 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspx[quote][b]peter.stanford 73369 (3/4/2011)[/b][hr]Just curious as to why you didn't consider the built-in T-SQL function called COALESCE. I often need to do what you describe and have found that COALESCE is relatively simple code and performs well. I use a function similar to the following:CREATE FUNCTION dbo.udf_Get_FG_Warehouse_List (@DocumentID int) RETURNS varchar(1000) AS BEGIN DECLARE @Warehouse_List varchar(1000)SELECT @Warehouse_List = COALESCE(@Warehouse_List + ', ', '') + a.NameFROM Warehouse_FG a JOIN NPF_FGWarehouse b on b.WarehouseID = a.IDWHERE b.NPFID = @DocumentIDORDER BY a.NameRETURN @Warehouse_ListEND[/quote]That works fine but is a form of RBAR because the function is necessarily a scalar function which refers to a table. That can make performance actually worse than a cursor.Sat, 05 Mar 2011 09:49:24 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspx[quote][b]arturv (3/4/2011)[/b][hr]Hi guys,why dont we keep it simple?CREATE TABLE #test (id INT NULL, NAME NVARCHAR(20) NULL)INSERT INTO #test ( id, name )VALUES ( 101, 'Apple' ), ( 102, 'Banana' ), ( 103, 'Orange' ), ( 104, 'Melon' ), ( 105, 'Grape' )SELECT * FROM #testDECLARE @fruit VARCHAR(MAX) SELECT @fruit = COALESCE(@fruit + '', '') + nameFROM #test SELECT Colors = @fruitIt take no efford at all.Artur[/quote]This is what I was thinking, and it what was several others already mentioned. Here is a "WITH" version of that one from Artur:[code="sql"]DECLARE @joined VARCHAR(max);WITH fruit as ( select 'Apple' as name, 101 as id union all select 'Banana' as name, 102 as id union all select 'Orange' as name, 103 as id union all select 'Melon' as name, 104 as id union all select 'Grape' as name, 105 as id )SELECT @joined=COALESCE(@joined+',', '')+name FROM fruitSELECT @joined[/code]Fri, 04 Mar 2011 21:07:28 GMTdavecasonhttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspxJust curious as to why you didn't consider the built-in T-SQL function called COALESCE. I often need to do what you describe and have found that COALESCE is relatively simple code and performs well. I use a function similar to the following:CREATE FUNCTION dbo.udf_Get_FG_Warehouse_List (@DocumentID int) RETURNS varchar(1000) AS BEGIN DECLARE @Warehouse_List varchar(1000)SELECT @Warehouse_List = COALESCE(@Warehouse_List + ', ', '') + a.NameFROM Warehouse_FG a JOIN NPF_FGWarehouse b on b.WarehouseID = a.IDWHERE b.NPFID = @DocumentIDORDER BY a.NameRETURN @Warehouse_ListENDFri, 04 Mar 2011 17:26:30 GMTpeter.stanford 73369http://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspxHi guys,why dont we keep it simple?CREATE TABLE #test (id INT NULL, NAME NVARCHAR(20) NULL)INSERT INTO #test ( id, name )VALUES ( 101, 'Apple' ), ( 102, 'Banana' ), ( 103, 'Orange' ), ( 104, 'Melon' ), ( 105, 'Grape' )SELECT * FROM #testDECLARE @fruit VARCHAR(MAX) SELECT @fruit = COALESCE(@fruit + '', '') + nameFROM #test SELECT Colors = @fruitIt take no efford at all.ArturFri, 04 Mar 2011 15:17:15 GMTarturvhttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspxSorry Guys, as I remember the CTE recursion works with depth <= 70Fri, 04 Mar 2011 13:21:11 GMTvkhabashttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspxMy solution where I posted the coalesce solution will eat up a cursor on performance. Another better solution than using a cursor is:Declare @tblName Table( tblNameId Int Identity(1,1), BclCode Varchar(10),)Declare @locMaxtblNameId Int, @locBclCode Varchar(10), @locRecdNo Int-- Get all the Client NumbersInsert Into @tblName Select BclCode From dbo.Client_Main Order By BclCodeSet @locMaxtblNameId = @@IdentitySet @locRecNo = 1While tblNameId <= @locMaxtblNameIdBegin Select @locBclCode = BclCode From @tblName Where tblNameId = @locRecNo Print 'BclCode selected was [' + @locBclCde & ']' -- Reduce the table size if you don't need the records again Delete @tblName Where tblNameId = @locRecNo Set @locRecNo = @locRecNo + 1 End-- This works even better in a stored proc where you can create an index for-- tblNamId and will eat a cursor up in performanceFri, 04 Mar 2011 11:36:58 GMTRHaverty 8478http://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspxI think this (using a cursor to iterate on the list) is the most reasonable solution for the newbie sql dveloper.Is there a performance issue in this way ?I think it shuold beat the recursion any time, and I'm not sure about the other one (xml path)Any insights ?Fri, 04 Mar 2011 10:58:45 GMTkaplan.danihttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspx[quote][b]Robert Bourdeau (3/4/2011)[/b][hr]It is a shame that TSQL lacks the CONNECT BY clause that is available with Oracle. All these complex solutions could be eliminated for many classes of row-joining queries.[/quote]I'll second that. I've been using the CONNECT BY for over 20 years. Can't live without it. Consequently, I have to re-invent it in some shape, matter, or form in SQL Server.Fri, 04 Mar 2011 10:52:58 GMTMauvehttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspxSQL2005 and higher have the [color=#FF0000]coalesce[/color] command.declare @remaining_string varchar(max)set @remaining_string = ''select @remaining_string = coalesce (@remaining_string + db.name + char(44), '') from database db (nolock)where db.id = Xorder by db.nameAt the end @remaining_string = 'name1,name2,name3,...,nameN'[url=http://msdn.microsoft.com/en-us/library/ms190349.aspx][/url]Fri, 04 Mar 2011 07:15:49 GMTdoug.davidsonhttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspxIt is a shame that TSQL lacks the CONNECT BY clause that is available with Oracle. All these complex solutions could be eliminated for many classes of row-joining queries.Fri, 04 Mar 2011 07:15:13 GMTRobert Bourdeauhttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspxA CTE way of doing it[code="sql"]-- Create a variable as tableDECLARE @Fruit TABLE ( ID INT, Name varchar(25))-- Populate the table with sample dataINSERT INTO @Fruit(ID, Name)VALUES (101,'Apple'), (102,'Banana'), (103,'Orange'), (104,'Melon'), (105,'Grape')-- Lets us create two cte's;WITH fruit(row, Name)-- fruit is just a sequencially numbered list of names-- When using ROW_NUMBER we eliminate IDs out of sequenceAS(-- ROW_NUMBER() gives us a sequencial list-- OVER (ORDER BY (SELECT NULL)) order by NULL, low cost order by SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), CAST(Name as varchar(max)) FROM @fruit), fruits(row, String)AS (-- Get the first row in the numbered list of names SELECT row, Name FROM fruit -- Get the lowest rownumber WHERE row = (SELECT MIN(row) FROM fruit)-- This is the magic UNION ALL-- Let us begin being recursive SELECT -- Fetched rownumber f2.row, -- Concatenate previous row with fetched row f1.String + ', ' + f2.Name-- This cte FROM fruits f1-- Previous cte, the numbered list of names INNER JOIN fruit f2 -- Next row ON f1.row + 1 = f2.row)-- Ready to return dataSELECT String FROM fruits -- To get the last rowWHERE row = (SELECT MAX(row) FROM fruits)[/code]What IF we had CONCAT as an aggregate function...But hey, we can.... SQL CLR is the way to go...[code="vb"]using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.Text;[Serializable][Microsoft.SqlServer.Server.SqlUserDefinedAggregate( Format.UserDefined, IsInvariantToOrder = false, IsInvariantToNulls = true, IsInvariantToDuplicates = false, MaxByteSize = -1 )]public struct concat :IBinarySerialize{ private StringBuilder _accumulator; private string _delimiter; public Boolean IsNull { get; private set; } public void Init() { _accumulator = new StringBuilder(); _delimiter = string.Empty; this.IsNull = true; } public void Accumulate(SqlString Value, SqlString Delimiter) { if (!Delimiter.IsNull & Delimiter.Value.Length > 0) { _delimiter = Delimiter.Value; if (_accumulator.Length > 0) _accumulator.Append(Delimiter.Value); } _accumulator.Append(Value.Value); if (Value.IsNull == false) this.IsNull = false; } public void Merge(concat Group) { if (_accumulator.Length > 0 & Group._accumulator.Length > 0) _accumulator.Append(_delimiter); _accumulator.Append(Group._accumulator.ToString()); } public SqlString Terminate() { // Put your code here return new SqlString(_accumulator.ToString()); } void IBinarySerialize.Read(System.IO.BinaryReader r) { _delimiter = r.ReadString(); _accumulator = new StringBuilder(r.ReadString()); if (_accumulator.Length != 0) this.IsNull = false; } void IBinarySerialize.Write(System.IO.BinaryWriter w) { w.Write(_delimiter); w.Write(_accumulator.ToString()); }}[/code]But I'm sorry for not reading through the thread... All above already said...Besides the little trickster in the cte I did with ROW_NUMBER() OVER (ORDER BY (SELECT NULL))The xml-way is the fastest performer compared to SQL CLR, about ten times faster on a table of 75000 words, and about 500 times faster than the cte-method, and some 1000 times faster than the variable-methodFri, 04 Mar 2011 07:13:45 GMTml-1106493http://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspxHere is alternative and the performance should be a little better as well:Declare @locBclCodeList Varchar(Max)Select @locBclCodeList = COALESCE(LTrim(RTrim(@locBclCodeList)) + ',' ,'') + [BclCode] From dbo.BclCodes Print ' @locBclCodeList [' + @locBclCodeList + ']'will creat a comma delimited list from a table of the BclCodes from each record in dbo.BclCodes.dbo.BclCodes contains:BclCodeId Int.BclCOde Varchar(10),MainBclCode Varchar(10),PrService Bit,HrService Bit,TcService Bit,ElecRept Bit,Several other Bit fields.I used this code to create a comma delimited list that I can use to select clients from other tables:Where (@locBclCodeList = '*' Or tblEarnings.BclCode In (Select Word From dbo.udfGetWord(@locBclCodeList, ',') Where Word = tblEarnings.BclCode))dbo.udfGetWord is a udf that will quickly search a delimit list of values.-- =========================================================================-- Source: udfGetWord-- Version: 1.0.0-- Author: R Haverty-- Date: 08/30/2008-- Revised: ---- Function: This udf was copied from GetAllWords2 and modified to return-- the Word Trimmed and in Upper Case.-- ----------------------------------------------------------------------------- =========================================================================Use TARSGO-- =========================================================================IF OBJECT_ID ('dbo.udfGetWord') IS NOT NULL DROP Function dbo.udfGetWordGO-- ==================================================================== =======-- Object: UserDefinedFunction [dbo].[GETALLWORDS2] -- Script Date: 08/30/2008 14:10:22SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- Author: Igor Nikiforov, Montreal, EMail: udfunctions@gmail.com -- GETALLWORDS2() User-Defined Function Inserts the words from a string into-- the table.-- GETALLWORDS2(@cString[, @cStringSplitting])-- Parameters-- *) @cString nvarchar(4000) - Specifies the string whose words will be -- inserted into the table @GETALLWORDS2. -- *) @cStringSplitting nvarchar(256) - Optional. Specifies the string used to -- separate words in @cString.-- The default delimiter is space.-- Note that GETALLWORDS2( ) uses @cStringSplitting as a single delimiter. -- Return Value table -- Remarks GETALLWORDS2() by default assumes that words are delimited by space. -- If you specify another string as delimiter, this function ignores spaces and -- uses only the specified string.---- Example-- *) Declare @cString nvarchar(4000), @nIndex smallint -- Select @cString = 'We hold these truths to be self-evident, -- that all men are created equal, that they are -- endowed by their Creator with certain unalienable Rights, -- that among these are Life, Liberty and the pursuit of -- Happiness.', @nIndex = 30-- Select WORD from dbo.GETALLWORDS2(@cString, default) where WORDNUM = @nIndex -- Displays 'Liberty'-- Select top 1 WORDNUM from dbo.GETALLWORDS2(@cString, default) order by WORDNUM desc -- Displays 35-- See Also GETWORDNUM() , GETWORDCOUNT() , GETALLWORDS() User-Defined Functions -- If no break string is specified, the function uses space to delimit words. CREATE function [dbo].[udfGetWord] ( @cString nvarchar(4000), @cStringSplitting nvarchar(256) = ' ' ) Returns @tblGetAllWords table ( WORDNUM smallint, WORD nvarchar(4000), STARTOFWORD smallint, LENGTHOFWORD smallint)-- ===========================================================================Begin -- udfGetWord -- ========================================================================= Declare @k smallint, @BegOfWord smallint, @wordcount smallint, @nEndString smallint, @nLenSrtingSplitting smallint, @flag bit -- ========================================================================= Select @cStringSplitting = isnull(@cStringSplitting, space(1)) , @cString = isnull(@cString, '') , @BegOfWord = 1, @wordcount = 1, @k = 0 , @flag = 0, @nEndString = 1 + datalength(@cString) / (case SQL_VARIANT_PROPERTY(@cString,'BaseType') When 'nvarchar' then 2 else 1 end), @nLenSrtingSplitting = datalength(@cStringSplitting) /(case SQL_VARIANT_PROPERTY(@cStringSplitting,'BaseType') When 'nvarchar' then 2 else 1 end) -- for unicode -- ========================================================================= While 1 > 0 Begin -- while 1 > 0 If @k - @BegOfWord > 0 Begin -- If @k - @BegOfWord > 0 Insert into @tblGetAllWords(WORDNUM, Word, STARTOFWORD, LENGTHOFWORD) Values (@wordcount, Upper(LTrim(RTrim(substring(@cString, @BegOfWord , @k - @BegOfWord)))), @BegOfWord, @k - @BegOfWord) Select @wordcount = @wordcount + 1, @BegOfWord = @k End -- If @k - @BegOfWord > 0 If @flag = 1 Break While Charindex( Substring(@cString, @BegOfWord, @nLenSrtingSplitting) COLLATE Latin1_General_BIN, @cStringSplitting COLLATE Latin1_General_BIN) > 0 -- skip break strings, if any Set @BegOfWord = @BegOfWord + @nLenSrtingSplitting select @k = Charindex(@cStringSplitting COLLATE Latin1_General_BIN, @cString COLLATE Latin1_General_BIN, @BegOfWord) If @k = 0 select @k = @nEndString, @flag = 1 End -- while 1 > 0-- =========================================================================== Return End -- udfGetWordFri, 04 Mar 2011 07:06:34 GMTRHaverty 8478http://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspxWhat I have never been able to figure out is why the SQL team has never implemented a standard built in aggregate function called ConcatenateToCSV or something.Fri, 04 Mar 2011 07:03:11 GMTdmorrisonhttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspxwhat about this query->select Name+',' from Fruit Order By Id for xml path('')Fri, 04 Mar 2011 06:22:41 GMTsukhendasshttp://www.sqlservercentral.com/Forums/Topic802508-1672-1.aspxAnother one CTE solution:[code="sql"]with fruit ([value], [key])as ( select cast('Apple' as nvarchar(max)) , 101 union all select 'Banana' , 102 union all select 'Orange' , 103 union all select 'Melon' , 104 union all select 'Grape' , 105 ),list as( select [key], [value] from fruit where [key] = 101 union all select f1.[key], list.[value] + ', ' + f1.[value] from fruit f1 inner join list on f1.[key] = list.[key] + 1)select top 1 list.[value]from listorder by list.[key] desc[/code]Fri, 04 Mar 2011 06:03:07 GMTlorefinding