InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 06:51:14 GMT20http://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxNice article.Tue, 25 Dec 2012 08:29:33 GMTNeha05http://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxDefinitely use XML over the variable-assignment syntax, as SQL Server supports XML. The variable-assignment syntax is not documented. It works, but is not supported.Sat, 06 Oct 2012 09:40:15 GMTBonz99http://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspx[url=http://blogs.inkeysolutions.com/2011/04/generating-comma-separated-list-through.html]This link [/url]has got a good compilation of the possible ways to do it.Tue, 02 Oct 2012 01:04:07 GMTvinaypugaliahttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxcan do by for xml element . and then eliminate traling ',' with stuff.no need of a variavle to store.direcectly can return that.Sat, 29 Sep 2012 13:57:49 GMTkrushna_debhttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxI just want to say thanks, I can use this to get rid of several while loops.Sat, 29 Sep 2012 12:10:45 GMTKing Conchhttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxHello.I'm used this one, without cursor:declare @sep varchar(1)declare @text varchar(max)set @sep=''select @text=@text + @sep + column1, @sep=',' from tableModification for distinct version was shown: replace table by (select distinct column1 from table) as tregardsJarekSat, 29 Sep 2012 06:38:24 GMTstoklosahttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspx[quote][b]Hugo Kornelis (9/28/2012)[/b][hr]However, the results returned may be completely different than what you expect, or want. For instance, when the optimizer chooses to use a parallel plan, it could just return the result from one of the threads, which you would probably consider incorrect. I don't think that the optimizer will at this time choose a parallel plan for queries of this type (I just spent a half hour trying very hard to get it to, but failed) - but since this is undocumented behaviour of the optimizer, that might change. And if a future change to the optimizer causes it to create a parallel plan for this query, your bug reports will probably be closed as "by design" - since "the correct behavior for an aggregate concatenation query is undefined."For me, this is enough reason to avoid this method. The XML method is a perfect replacement - and this method IS documented, and hence guaranteed.[/quote]Thanks for the reply; these are excellent points. I still read the KB article as specifically having to do with the ORDER BY issue, but I'm coming around to seeing your larger point. Today, the ORDER BY issue may be the only way to expose the problem with the "aggregate concatenation query" approach, but there's no guarantee that will be true tomorrow.And as you point out, given that there's a perfectly sound alternative, we'd all certainly be wise to explore the XML method. That will improve my future code; whether I get time to refactor my existing stuff is another matter. :hehe:Fri, 28 Sep 2012 17:46:47 GMTBowlOfCerealhttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspx[quote][b]BowlOfCereal (9/28/2012)[/b][hr]There's been a lot of discussion in this (oddly) revived thread today.[/quote]The reason for the revival of the thread is that the article was prominently featured in the newsletter.[quote]According to Microsoft's KB article and Connect comments: the unpredictable, unsupported behavior occurs "when you apply any operators or expressions to the ORDER BY clause of aggregate concatenation queries". I understand that (or at least I think I do). But this has nothing to do with building a comma delimited string. The original article said nothing about applying operators to an ORDER BY clause. So, my question: is it true that the method presented in this article is "known to potentially return incorrect results"?[/quote]From [url=http://support.microsoft.com/kb/287515/en-us]the relevant Microsoft Knowledge Base article[/url]: "The correct behavior for an aggregate concatenation query is undefined."If the correct behaviour is undefined, then it's impopssible to tell if a given result is incorrect. Ergo, the method presented in this article can by definition never return "incorrect" results.However, the results returned may be completely different than what you expect, or want. For instance, when the optimizer chooses to use a parallel plan, it could just return the result from one of the threads, which you would probably consider incorrect. I don't think that the optimizer will at this time choose a parallel plan for queries of this type (I just spent a half hour trying very hard to get it to, but failed) - but since this is undocumented behaviour of the optimizer, that might change. And if a future change to the optimizer causes it to create a parallel plan for this query, your bug reports will probably be closed as "by design" - since "the correct behavior for an aggregate concatenation query is undefined."For me, this is enough reason to avoid this method. The XML method is a perfect replacement - and this method IS documented, and hence guaranteed.Fri, 28 Sep 2012 17:05:15 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxThere's been a lot of discussion in this (oddly) revived thread today. I want to attempt to clarify something regarding a few comments in this thread, for my own benefit at least: [quote][b]Hugo Kornelis (9/28/2012)[/b][hr]the method presented in this article is not the better and faster way, because it is undocumented, unsupported, and known to potentially return incorrect results. I must say that I am really disappointed to see this article being rerun, as that suggests that SQLServerCentral.com supports this method.[/quote][quote][b]Mauve (9/28/2012)[/b][hr]Using the [improper] technique of SELECTing a column and performing an operation on it such as building a comma delimited string will yield [u][i]unpredictable[/u][/i] results![/quote]According to Microsoft's KB article and Connect comments: the unpredictable, unsupported behavior occurs "when you apply any operators or expressions to the ORDER BY clause of aggregate concatenation queries". I understand that (or at least I think I do). But this has nothing to do with building a comma delimited string. The original article said nothing about applying operators to an ORDER BY clause. So, my question: is it true that the method presented in this article is "known to potentially return incorrect results"? This is an important distinction to me - I have used the trick described in the original article a few times in production code. In fact, I think we're all guilty of "SELECTing a column and performing an operation on it such as building a comma delimited string" from time to time (ha ha).I don't want to add fuel to the debate over which approach is most efficient. I'm simply asking about the reliability and predictability of the return values.Fri, 28 Sep 2012 15:48:20 GMTBowlOfCerealhttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxI have used this same FOR XML technique for various delimited string manipulation in TSQL. It works well and it does have documented quite a bit of "How to" available, google search or just MSDN help sheds light on how to do it. Carl DemeloPresidentProfessional Database Serviceshttp://www.SQLSavvy.comCarl.Demelo@SQLSavvy.comOffice: (480) 331-1302Fri, 28 Sep 2012 13:58:00 GMTCarlDemelohttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxYou can avoid the XML characters problem in the For XML Path version by using the value() method on the XML.[code="sql"]IF OBJECT_ID(N'tempdb..#T') IS NOT NULL DROP TABLE #T; SELECT *INTO #TFROM ( VALUES ( '1>'), ( '2&'), ( '3/') ) AS TCV (Col);SELECT STUFF((SELECT ',' + Col FROM #T ORDER BY Col DESC FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '');[/code]You can use either varchar or nvarchar in the value() method. Either works. I usually use nvarchar because the company I work for is multinational and has to deal with extended character set data.The Concat/IsNull method doesn't work with Order By, is prone to string truncation, breaks if the query goes parallel (multiple threads), and is subject to change without notice if MS does work on the query engine. The XML version has none of those weaknesses. If you're using a version of SQL Server that doesn't support For XML (pre-SQL 2000), then use a cursor, not the Concat method, unless you want the code to sometimes work and sometimes not.Note: My sample table is built using an SQL 2008+ feature, Table Value Constructor. If you want to test the final query in SQL 2005 or SQL 2000, build the sample data using Union All instead.Fri, 28 Sep 2012 13:10:26 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspx[quote][b]Ninja's_RGR'us (3/21/2011)[/b][hr]-snip-And just for fun, a recursive CTE solution! :hehe:[code="sql"]DECLARE @t varchar(max);SET @t = '';WITH rectags AS ( SELECT TOP 1 Tag FROM Tags ORDER BY Tag UNION ALL ( SELECT ', ' + Tag FROM Tags EXCEPT SELECT TOP 1 ', ' + Tag FROM Tags ORDER BY Tag ))SELECT @t += Tag FROM rectagsSELECT @t[/code][/quote]Could someone give an explanation for this "recursive" cte? I don't see how it's recursive or how it works (and it does work).Fri, 28 Sep 2012 10:54:34 GMTjohn.morenohttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxBecause @MyStatusList is never NULL, the versions with ISNULL and COALESCE do not work. But a CASE statement does work.[code]SELECT @MyStatusList = ISNULL(@MyStatusList,'') + case when len(@myStatusList) = 0 then '' else ',' end + StatusDesc FROM (SELECT DISTINCT StatusDesc FROM MyStatus) x[/code]Fri, 28 Sep 2012 10:18:32 GMTtobehttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxA different approach, with its own pros and cons, is to use a CLR aggregate function to generate the comma-separated list. Microsoft provides the "Concatenate" function for this purpose in its StringUtilities CLR code sample. This method requires adding an assembly to the database, which may not be possible in some circumstances.Fri, 28 Sep 2012 09:32:56 GMTMegan Brookshttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxThis is called a [b]Group_Concat in MySQL[/b]. SQL Server does not provide such functionality. It has to be mimicked with the @var method you describe. Here is a similar article on the technique: http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.htmlFri, 28 Sep 2012 07:46:41 GMTvirtualjoshhttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxPSSorry, this time including some sample dataCREATE TABLE [dbo].[MyStatus]( [Status_Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL, [StatusDesc] [varchar](25) NULL,)INSERT INTO MyStatus VALUES ('Active')INSERT INTO MyStatus VALUES ('OnHold')INSERT INTO MyStatus VALUES ('Disabled')INSERT INTO MyStatus VALUES ('Closed')DECLARE @MyStatusList VARCHAR(MAX)SET @MyStatusList = ''SELECT @MyStatusList = @MyStatusList + CONVERT(VARCHAR(20), StatusDesc)FROM MyStatus-- ORDER BY LEN(StatusDesc) DESC, StatusDesc ASCSELECT @MyStatusList -- will give an unexpectedly different result if you uncomment the ORDER BY Fri, 28 Sep 2012 07:41:27 GMTstephen.sarrehttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxI tried each of the queries on the article and could only get the cursor to work. Other than that the coalesce by josef worked as well. Did anyone else have this issue?Fri, 28 Sep 2012 07:36:03 GMTjvalentine 25324http://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxselect STUFF ( ( SELECT ',' + StatusDesc FROM MyStatusList FOR XML PATH('') ORDER BY Status_Id ), 1, 1, '' )Fri, 28 Sep 2012 07:34:17 GMTArnold Liebermanhttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxThe article posted was very nice, and we used that technique for years, and it was great, and definitely cut down on the amount of code necessary. Thank you to the author. I don't think that technique is documented in SQL Server.We found that we often needed this type of ability (i.e. string aggregation) on the fly, or within a view, to return a comma separated list of values, for every row.We used to write functions to handle that, but it was terribly inefficient b/c it would essentially query once per row, within the overall query.Oracle introduced user defined aggregates a while ago, and Microsoft added support for them via the CLR recently. It's awesome. If you haven't used it, you're missing out. It's one of my favorite new features of SQL Server in the past many years.It allows you to use your function just like you would a normal aggregate, either in an ad-hoc query or a view, like this...select some_field, dbo.collect(some_other_field, ', ') as some_other_field_listfrom some_tablegroup by some_fieldOne of the natural advantages of this approach, aside from performance, is that any complicated filtering you had going in your from and where clause is automatically involved in what is aggregated/collected. Whereas, if you had to call a function in the select, all you could do was pass some kind of id to try to simulate the context. That's hard, clunky, and sometimes actually 100% impossible.Here is the code to our current version of what we call 'collect', my new best friend.Beats the pants off of the other methods. We've found we need to enhance it some, to handle collecting other things, like dates, and numbers, but for now, this version works well. One thing, we did find that occasionally it will get messed up if you don't set the max degrees of parallelism to 1 (i.e. option maxdop(1)) at the end of you query. We're going to try to re-write it to deal with parallel query. It's a little complicated but can be done.Anyway, here's the version we have an use, which can be easily compiled into a DLL and then loaded into your database as an assembly. Can post the surrounding information if anyone is interested. You do need a little wrapper aggregate function in T-SQL as well.using System;using System.Collections.Generic;using System.Data.SqlTypes;using System.IO;using System.Linq;using Microsoft.SqlServer.Server;namespace WitsCLR.UserDefinedAggregates{ [Serializable] [SqlUserDefinedAggregate ( Format.UserDefined, MaxByteSize = -1, IsInvariantToDuplicates = true, IsInvariantToNulls = true, IsInvariantToOrder = false, IsNullIfEmpty = true ) ] // *************************************************************************** // * public class Collect : IBinarySerialize // *************************************************************************** public class Collect : IBinarySerialize { private string delimiter; private List<string> result; // *************************************************************************** // * public void Init // *************************************************************************** public void Init() { this.delimiter = ""; this.result = new List<string>(); } // *************************************************************************** // * public void Accumulate // *************************************************************************** public void Accumulate(SqlString expression, string delimiter) { if ((expression.IsNull) || (delimiter == null)) { return; } this.delimiter = delimiter; this.result.Add(expression.Value); } // *************************************************************************** // * public void Merge // *************************************************************************** public void Merge(Collect other) { this.result.AddRange(other.result.ToArray()); } // *************************************************************************** // * public string Terminate // *************************************************************************** public string Terminate() { if (this.result.Count == 0) { return null; } IEnumerable<string> distinctResult = this.result.Distinct(); this.result = distinctResult.ToList<string>(); this.result.Sort(); return string.Join(this.delimiter, this.result.ToArray()); } // *************************************************************************** // * public void Read // *************************************************************************** public void Read(BinaryReader r) { this.delimiter = r.ReadString(); int itemCount = r.ReadInt32(); this.result = new List<string>(itemCount); for (int i = 0; i < itemCount; i++) { this.result.Add(r.ReadString()); } } // *************************************************************************** // * public void Write // *************************************************************************** public void Write(BinaryWriter w) { w.Write(this.delimiter); w.Write(this.result.Count); foreach (string s in this.result) { w.Write(s); } } }}And the T-SQL part...create aggregate collect( @expression nvarchar(max), @delimiter nvarchar(max))returns nvarchar(max)external name WitsCLR.[WitsCLR.UserDefinedAggregates.Collect]goHope this helps.RyanFri, 28 Sep 2012 07:28:41 GMTrwhite-957607http://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxYou need to remember that string manipulation in windows is horribly slow. I wrote an article on creating delimited lists using the FOR XML in 2011 [url=http://www.sqlservercentral.com/articles/comma+separated+list/71700/][u]here[/u][/url]. Using this method, I've seen a process that took 30 minutes go to < 1 second... can't beat that kind of boost.Fri, 28 Sep 2012 07:10:31 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxHi Suresh,Thanks for your post but there are some possible problems if you start to use ORDER BY.Seehttp://stackoverflow.com/questions/9938853/order-by-when-concating-a-varchar-does-not-work-as-expected?rq=1for exampleDECLARE @MyStatusList VARCHAR(MAX)SET @MyStatusList = ''SELECT @MyStatusList = @MyStatusList + CONVERT(VARCHAR(20), Field)FROM Table1-- ORDER BY LEN(Field) DESC, Field ASCSELECT @MyStatusList will give an unexpectedly different result fromDECLARE @MyStatusList VARCHAR(MAX)SET @MyStatusList = ''SELECT @MyStatusList = @MyStatusList + CONVERT(VARCHAR(20), Field)FROM Table1ORDER BY LEN(Field) DESC, Field ASCSELECT @MyStatusListSteve Fri, 28 Sep 2012 07:03:38 GMTstephen.sarrehttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspx[quote][b]Hugo Kornelis (3/21/2011)[/b][hr][quote][b]stefan.hoffmann (3/21/2011)[/b][hr]hi Hugo,I'd like to disagree. While the last example uses only @var = column the explanation above it clearly states:"If a SELECT statement returns more than one row and the variable references a nonscalar expression, the variable is set to the value returned for the expression in the last row of the result set. For example, in the following batch @EmpIDVariable is set to the BusinessEntityID value of the last row returned, which is 1:""references a nonscalar expression" should describe exactly our SELECT @var = @var + column FROM table ORDER BY columnOrExpression scenario. Or is there any subtle interpretation I don't get here, maybe my English is not sufficient enough...[/quote]Hi Stefan,I must admit that it is not as clear-cut as I thought it was. I think I remember that there was an explicit disclaimer for the @var = @var + col case, but I can't find it anymore - maybe I am confisunng this with the equally dangerous SET @var = col = @var + othercol extension to the UPDATE statement.Basically, if you take the wording very literally, then yes you are right: the variable should be set to the expression in the last row. And if the column value happens to be 'A' for the last row, then the value of @var after SELECT @var = @var + col FROM SomeTable should be set to @var + 'A'. But how does this help us define the expected output? What value for @var has to be used by SQL Server when processing this last row? The value that resulted from the second to last row? The value @var had before the query was started? Or even something else (imagine a parallel plan that uses three processors to process the three partitions of partitioned table SomeTable).Anyway, I did find another useful link - a [url=http://support.microsoft.com/kb/287515/en-gb]Microsoft Knowledge Base article[/url] that excplicitly warns that the results of this kind of queries ("aggregate concatenation queries") are undefined - see especially the first sentence after the "Cause" heading: "The correct behavior for an aggregate concatenation query is undefined."[/quote]I agree with Hugo.Using the [improper] technique of SELECTing a column and performing an operation on it such as building a comma delimited string will yield [u][i]unpredictable[/u][/i] results! I have been burnt by this! Try it on the contents of a table variable.I have also used the XML method to create comma-delimited values with partial success. [i]If[/i] the source data does not contain any invalid XML characters [u]and[/u] you will accept encoded values for certain characters in the final result (e.g., &lt&#59;, &gt&#59;, &amp&#59;, etc.) then it does work. So use the proper coding technique. Use a cursor. Otherwise you are just coding a "land mine" / "time bomb" that will have to be fixed later. I know, as I've had to fix them.Fri, 28 Sep 2012 06:57:31 GMTMauvehttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspx/*-------STUFF approach -----------------*/SELECT top(1) STUFF(( SELECT ', ' + CAST([StatusDesc] AS VARCHAR(100)) FROM MyStatus FOR XML PATH ('')) ,1,2,'') AS NameValues FROM MyStatus Results /*------- end STUFF approach -----------------*/Fri, 28 Sep 2012 04:56:23 GMTnicola.dibacciohttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxI feel this could be done using stuff and for xml(path).We need not trim the string in the end.Fri, 28 Sep 2012 04:55:38 GMTsurmountfearhttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxI always use the stuff function to make a string array seperated by a delimeter: declare @mystatus as table (stat_id int identity(1,1), stat_desc varchar(25) null); insert @mystatus select 'Active' union all select 'Onhold' union all select 'Disabled' union all select 'Closed' -- stuff values into array declare @my_stat_list varchar(250) set @my_stat_list = stuff((select /*distinct*/ ',' + stat_desc from @mystatus order by stat_desc for xml path ('')),1,1,'') select @my_stat_listFri, 28 Sep 2012 04:39:17 GMTUpserthttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspx[quote][b]Knut Boehnert (9/28/2012)[/b][hr]A general statement like this is more dangerous than the use of cursors. :-PCheck out http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx. While specialised in its case it counteracts the above general statement and thus makes it invalid. Sorry.[/quote]As the author of the blog post you link to, I must contradict you.That blog post does describe how you can get the best performance out of cursors. But it also warns (and supports, by giving performance figures) that set-based SQL is lots, lots, and lots faster. Some relevant quotes fro that blog post:"The best way to optimize performance of a cursor is, of course, to rip it out and replace it with set-based logic."(...)"With these options, execution time went down (...) to 3.3 to 3.4 seconds. Of course, none of those come even close to the 0.2 seconds of the set-based equivalent for this test case"(...)"If you have to optimize a cursor for performance, keep the following considerations in mind:1. Always try to replace the cursor by a set-based equivalent first. If you fail to see how, do not hesitate to ask (...)"Yes, there are cases where a cursor is indeed the fastest (or, should I say, least slow) option.No, concatenating strings from a result set is not one of them. There are better and faster ways to do this.No, the method presented in this article is not the better and faster way, because it is undocumented, unsupported, and known to potentially return incorrect results. I must say that I am really disappointed to see this article being rerun, as that suggests that SQLServerCentral.com supports this method.But the FOR XML method (presented in various posts in this topic) [b][i]is[/i][/b] documented and supported, and a whole lot faster than even the best optimized cursor.Fri, 28 Sep 2012 04:20:14 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspx[quote][b]Jason-299789 (3/21/2011)[/b][hr]Cursors in MS T-SQL should be avoided unless absolutely necessary as they are extremely slow and inefficent.Using one of the methods mentioned in previous posts is a better and more efficent alternative to the method you mention, my personal preference is the XML conversion with a nested Replace.[/quote]A general statement like this is more dangerous than the use of cursors. :-PCheck out http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx. While specialised in its case it counteracts the above general statement and thus makes it invalid. Sorry.Fri, 28 Sep 2012 03:55:23 GMTKnut Boehnerthttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxHiThis is the same that I posted, please see here:[url=http://www.sqlservercentral.com/scripts/Select+to+array/91801/][/url]like brian.healy had posted you can use xml[code="sql"]select SUBSTRING((SELECT (',' + id) FROM teste ORDER BY id FOR XML PATH('') ), 2, 4000)[/code]adapted to your code:[code="sql"]select SUBSTRING((SELECT (',' + [StatusDesc]) FROM MyStatus ORDER BY [StatusDesc] FOR XML PATH('') ), 2, 4000)[/code]with distinct...you may use:[code="sql"]select SUBSTRING((SELECT (',' + T.[StatusDesc]) FROM (select distinct [StatusDesc] from MyStatus) T ORDER BY T.[StatusDesc] FOR XML PATH('') ), 2, 4000)[/code]But Thanks for your postLFCostFri, 28 Sep 2012 03:16:32 GMTlfcosthttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxI thought it looked good and tried to replace an existing query with this method, but my original method to 0 seconds and this method needed 41 seconds.Original method :CREATE FUNCTION [dbo].[AgentsDropDown] (@Exclude varchar(max))RETURNS varchar(max)ASBEGIN -- Declare the return variable here DECLARE @List varchar(max) SELECT @List = '<select name="AgentsList" class="formboxes" onchange="" Multiple ><option value="0">NO AGENT</option>' + STUFF(g.y, 1, 0, '') + '</select>' FROM (SELECT DISTINCT CorporateKey as "@value",Name AS "data()" FROM dbo.Names WHERE (CHARINDEX(',', dbo.Names.Name) > 0) AND (LEN(dbo.Names.CorporateKey) = 6) OR(LEN(dbo.Names.CorporateKey) = 10) AND CorporateKey NOT IN (@Exclude) ORDER BY Name FOR XML PATH('option')) AS g(y) RETURN @ListENDNew method:CREATE FUNCTION [dbo].[StatusList] (@Exclude varchar(max))RETURNS varchar(max)ASBEGIN DECLARE @List varchar(max) SET @List = '<select name="AgentsList" class="formboxes" onchange="" Multiple ><option value="0">NO AGENT</option>' SELECT @List = ISNULL(@List,'') + '<option value="' + [CorporateKey] + '">' + [Name] + '</option>' FROM (SELECT DISTINCT TOP 100 percent CorporateKey ,Name FROM dbo.Names WHERE (CHARINDEX(',', dbo.Names.Name) > 0) AND (LEN(dbo.Names.CorporateKey) = 6) OR(LEN(dbo.Names.CorporateKey) = 10) AND CorporateKey NOT IN (@Exclude) ORDER BY Name ) AS x RETURN @ListENDFri, 28 Sep 2012 02:39:43 GMTjohn.baconhttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxNobody in this thread seems to have mentioned this alternative technique. It is not as fast as the XML technique but it is very simple, and can be elaborated for some very handy manipulations such as a pivot, or putting delimited lists in markup. All the techniques except the XML one work safest with table variables.[font="Courier New"][size="2"][color="black"][/color][color="blue"]SELECT [/color][color="#434343"]@String[/color][color="blue"]=[/color][color="magenta"]REPLACE[/color][color="gray"]([/color][color="#434343"]@String[/color][color="gray"],[/color][color="red"]'%'[/color][color="gray"],[/color][color="black"]Value[/color][color="gray"]+[/color][color="red"]',%'[/color][color="gray"]) [/color][color="blue"]FROM [/color][color="black"]YourTable[/color][/size][/font]as in...[font="Courier New"][size="2"][color="blue"]DECLARE [/color][color="#434343"]@string [/color][color="blue"]VARCHAR[/color][color="gray"]([/color][color="black"]8000[/color][color="gray"])[/color][color="blue"]SELECT [/color][color="#434343"]@string[/color][color="blue"]=[/color][color="red"]'%' [/color][color="green"]--or whatever you choose for your marker[/color][color="blue"]SELECT [/color][color="#434343"]@String[/color][color="blue"]=[/color][color="magenta"]REPLACE[/color][color="gray"]([/color][color="#434343"]@String[/color][color="gray"],[/color][color="red"]'%'[/color][color="gray"],[/color][color="black"]number[/color][color="gray"]+[/color][color="red"]',%'[/color][color="gray"])[/color][color="blue"]FROM [/color][color="gray"]([/color][color="blue"]SELECT [/color][color="red"]'un' [/color][color="blue"]AS [/color][color="black"][number][/color][color="blue"]UNION [/color][color="gray"]ALL [/color][color="blue"]SELECT [/color][color="red"]'dau' [/color][color="blue"]UNION [/color][color="gray"]ALL [/color][color="blue"]SELECT [/color][color="red"]'tri'[/color][color="blue"]UNION [/color][color="gray"]ALL [/color][color="blue"]SELECT [/color][color="red"]'pedwar'[/color][color="blue"]UNION [/color][color="gray"]ALL [/color][color="blue"]SELECT [/color][color="red"]'pump'[/color][color="blue"]UNION [/color][color="gray"]ALL [/color][color="blue"]SELECT [/color][color="red"]'chwech'[/color][color="blue"]UNION [/color][color="gray"]ALL [/color][color="blue"]SELECT [/color][color="red"]'saith'[/color][color="blue"]UNION [/color][color="gray"]ALL [/color][color="blue"]SELECT [/color][color="red"]'wyth'[/color][color="blue"]UNION [/color][color="gray"]ALL [/color][color="blue"]SELECT [/color][color="red"]'naw'[/color][color="blue"]UNION [/color][color="gray"]ALL [/color][color="blue"]SELECT [/color][color="red"]'deg'[/color][color="gray"]) [/color][color="black"][welsh numbers][/color][color="blue"]SELECT [/color][color="magenta"]REPLACE[/color][color="gray"]([/color][color="#434343"]@String[/color][color="gray"],[/color][color="red"]',%'[/color][color="gray"],[/color][color="red"]''[/color][color="gray"])[/color][/size][/font]Giving ...------------------------------------------------------------un,dau,tri,pedwar,pump,chwech,saith,wyth,naw,deg(1 row(s) affected)Fri, 28 Sep 2012 02:22:58 GMTPhil Factorhttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxHi All,Can anyone please elaborate on how it's concatenating the row data in single column...Sorry if it's a basic question but i'll be highly obliged if any one could explain...:-)[b]SET @MyStatusList = SUBSTRING(@MyStatusList, 1, LEN(@MyStatusList)-1) [/b]Fri, 28 Sep 2012 01:34:11 GMTnaqui.ahmedhttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxWhen using the FOR XML method described in the post by feeza_ibrahim (third post on page 1 of this topic) make sure the column does not contain other than preceding or trailing spaces as in the REPLACE all such spaces are replaced by a separator.e.g. the Status 'On Hold' will return two values 'On' and 'Hold' in the CSV-stringFri, 28 Sep 2012 01:29:51 GMTMarc Kuppenshttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxThanks for the article. I have used the For XML approach as well as the Coalesce approach in the past. I also recently blogged about the two methods, [u][b][url=http://jasonbrimhall.info/2011/03/01/delimiting-results/]here[/url][/b][/u].Tue, 22 Mar 2011 10:21:44 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxYou can also use this one...DECLARE @MyStatusList VARCHAR(1000)SET @MyStatusList = ''SELECT @MyStatusList = @MyStatusList + CASE WHEN @MyStatusList = '' THEN '' ELSE ', ' END + StatusDesc FROM MyStatusSELECT @MyStatusListTue, 22 Mar 2011 05:19:46 GMTVinod Chobhehttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxHi David,You don't have loose nothing.There is only a redundant test in the second line of the article.The first line of code is: SET @MyStatusList = '' so @MyStatusList is never NULL.so in the second line of code ISNULL(@MyStatusList,'') is redundant because is always false.If you not set a variable default value is NULL and good version should be:SELECT @MyStatusList = COALESCE(@MyStatusList + ',', '') + StatusDesc FROM MyStatusor (is the same)SELECT @MyStatusList = ISNULL(@MyStatusList + ',','') + StatusDesc FROM MyStatusTue, 22 Mar 2011 03:53:49 GMTAlberto Zoppohttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxhi David,in the article the varaibale is initialized with '' - an empty string. This is not necessary. When declaring a variable with out extra value assignment, it gets initialized automatically with NULL. This is where COALESCE(@var + ', ', '') OR ISNULL(@var + ', ', '') does its magic: The first call to COALESCE/ISNULL is COALESCE(NULL + ', ', ''), which gets evaluated to COALESCE(NULL, '') and this returns ''.The second and all subsequent calls are like COALESCE('value' + ', ', ''), which returns 'value, '. So you only append the comma in the case when you append a value to an existing value. This means that no leading or trailing comma exists, which must be removed after concatenating the values.[code="sql"]USE AdventureWorksLT ;GO-- Initialized with empty stringDECLARE @tablenames NVARCHAR(MAX)= '' ;SELECT @tablenames = @tablenames + ', ' + nameFROM sys.tables ;SELECT @tablenames ;SELECT STUFF(@tablenames, 1, 2, '') ;-- Initialized automatically with NULLDECLARE @tablenames2 NVARCHAR(MAX) ;SELECT @tablenames2 = COALESCE(@tablenames2 + ', ', '') + nameFROM sys.tables ;SELECT @tablenames2 ;[/code]Tue, 22 Mar 2011 03:47:10 GMTstefan.hoffmannhttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxI think these are the articles describing the problem with this technique being non-deterministic. Complete with examples that may or may not work depending upon your optimiser, the version of mssql you're running and the current phase of the moon.[url=http://blog.sqlauthority.com/2009/09/29/sql-server-interesting-observation-execution-plan-and-results-of-aggregate-concatenation-queries/]http://blog.sqlauthority.com/2009/09/29/sql-server-interesting-observation-execution-plan-and-results-of-aggregate-concatenation-queries/[/url][url=http://blog.sqlauthority.com/2009/09/20/sql-server-execution-plan-and-results-of-aggregate-concatenation-queries-depend-upon-expression-location/]http://blog.sqlauthority.com/2009/09/20/sql-server-execution-plan-and-results-of-aggregate-concatenation-queries-depend-upon-expression-location/[/url]Tue, 22 Mar 2011 03:44:38 GMTChris Chilvershttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxA useful article and discussion. However I do not understand the point of [font="Courier New"]ISNULL(@MyStatusList,'')[/font] in the method given in the article. As the first line of code is [code="plain"]SET @MyStatusList = ''[/code] [font="Courier New"]@MyStatusList[/font] is never NULL.Similarly, in Mansfield's suggestion above, the preceding line would have to be [code="plain"]SET @MyStatusList[/code] for the [font="Courier New"]ISNULL[/font] to work.What have I missed?Tue, 22 Mar 2011 03:05:53 GMTDavid Datahttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxIn the sample of the article with the init of @MyStatusList='' the test ISNULL(@MyStatusList,'') is not necessary: SET @MyStatusList = ''SELECT @MyStatusList = ISNULL(@MyStatusList,'') + StatusDesc + ',' FROM MyStatusBest version, without drop the trailing comma at the end is:SELECT @MyStatusList = COALESCE(@MyStatusList + ',', '') + StatusDesc FROM MyStatusTue, 22 Mar 2011 02:56:53 GMTAlberto Zoppohttp://www.sqlservercentral.com/Forums/Topic1081065-2921-1.aspxRemove last comma or dilimiter without substring ... Check it out here at [url=http://www.sqlsuperfast.com/post/2011/02/17/T-SQL-to-Convert-rows-into-single-column.aspx]www.sqlsuperfast.com[/url]Mon, 21 Mar 2011 23:58:38 GMTa.rajmane