{"id":410,"date":"2008-07-31T00:00:00","date_gmt":"2008-07-31T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/concatenating-row-values-in-transact-sql\/"},"modified":"2026-04-17T13:05:09","modified_gmt":"2026-04-17T13:05:09","slug":"concatenating-row-values-in-transact-sql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/concatenating-row-values-in-transact-sql\/","title":{"rendered":"Concatenate Row Values in SQL Server: STRING_AGG and T-SQL Methods"},"content":{"rendered":"\n<div id=\"pretty\">\n<p>To concatenate row values in SQL Server &#8211; combining multiple rows into a single delimited string &#8211; use STRING_AGG() if you are on SQL Server 2017 or later. It is the cleanest, most readable approach and handles grouping, ordering, and distinct values natively. For SQL Server 2016 and earlier, the FOR XML PATH trick is the most reliable method. This article documents both in detail, alongside recursive CTEs, CLR aggregates, and cursor-based approaches, with guidance on which to use based on your SQL Server version and dataset size.<\/p>\n<h2>Contents<\/h2>\n<ul>\n<li><a href=\"#Toc205129480\">Introduction. <\/a><\/li>\n<li><a href=\"#Toc205129481\">A core issue. <\/a><\/li>\n<li><a href=\"#Toc205129482\">Considerations <\/a><\/li>\n<li><a href=\"#Toc205129483\">Concatenating values when the number of items is small and known beforehand<\/a><\/li>\n<li><a href=\"#Toc205129484\">Concatenating values when the number of items is not known. <\/a>\n<ul>\n<li><a href=\"#Toc205129485\">Recursive CTE methods <\/a><\/li>\n<li><a href=\"#_Toc205129486\">The blackbox XML methods <\/a><\/li>\n<li><a href=\"#_Toc205129487\">Using Common Language Runtime. <\/a><\/li>\n<li><a href=\"#_Toc205129488\">Scalar UDF with recursion. <\/a><\/li>\n<li><a href=\"#_Toc205129489\">Table valued UDF with a WHILE loop. <\/a><\/li>\n<li><a href=\"#Toc205129490\">Dynamic SQL<\/a><\/li>\n<li><a href=\"#Toc205129491\">The Cursor approach. <\/a><\/li>\n<\/ul>\n<\/li>\n<li><a href=\"#Toc205129492\">Unreliable approaches <\/a>\n<ul>\n<li><a href=\"#_Toc205129493\">Scalar UDF with t-SQL update extension. <\/a><\/li>\n<li><a href=\"#_Toc205129494\">Scalar UDF with variable concatenation in SELECT<\/a><\/li>\n<\/ul>\n<\/li>\n<li><a href=\"#_Toc205129495\">Conclusion. <\/a><\/li>\n<li><a href=\"#_Toc205129496\">References <\/a><\/li>\n<li><a href=\"#_Toc205129497\">Acknowledgements <\/a><\/li>\n<\/ul>\n<h2 id=\"Toc205129480\">Introduction<\/h2>\n<p>Many a time, SQL programmers are faced with a requirement to generate report-like resultsets directly from a Transact SQL query. In most cases, the requirement arises from the fact that there neither sufficient tools nor in-house expertise to develop tools that can extract the data as a resultset, and then massage the data in the desired display format. Quite often folks are confused about the potential of breaking relational fundamentals such as the First Normal Form or the scalar nature of typed values. (Talking about 1NF violations in a language like SQL which lacks sufficient domain support, allows <b>NULL<\/b>s and supports duplicates is somewhat ironic to begin with, but that is a topic which requires detailed explanations.)<\/p>\n<figure><img decoding=\"async\" class=\"float-right\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/539-ConcatenateClip2.jpg\" alt=\"539-ConcatenateClip2.jpg\" \/><\/figure>\n<p>\u00a0<\/p>\n<p>By &#8216;Concatenating row values&#8217; we mean this: <br \/>You have a table, view or result that looks like this&#8230;<br \/>&#8230;and you wish to have a resultset like the one below:<\/p>\n<p>In this example we are accessing the sample NorthWind database and using the following SQL<\/p>\n<p>\u00a0<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT CategoryId, ProductName \n\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Northwind..Products\n<\/pre>\n<p>The objective is to return a resultset with two columns, one with the Category Identifier, and the other with a concatenated list of all the Product Names separated by a delimiting character: such as a comma.<\/p>\n<figure><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/539-ConcatenateClip.jpg\" alt=\"539-ConcatenateClip.jpg\" width=\"610\" height=\"172\" \/><\/figure>\n<p>\u00a0<\/p>\n<p>Concatenating column values or expressions from multiple rows are usually best done in a client side application language, since the string manipulation capabilities of Transact SQL and SQL based DBMSs are somewhat limited. However, you can do these using different approaches in Transact SQL, but it is best to avoid such methods in long-term solutions<\/p>\n<h2 id=\"Toc205129481\">A core issue<\/h2>\n<p>Even though SQL, in general, deviates considerably from the relational model, its reliance on certain core aspects of relational foundations makes SQL functional and powerful. One such core aspect is the set based nature of SQL expressions (well, multi-sets to be exact, but for the given context let us ignore the issue of duplication). The primary idea is that tables are unordered and therefore the resultsets of any query that does not have an explicit <b>ORDER BY<\/b> clause is unordered as well. In other words, the rows in a resultset of a query do not have a prescribed position, unless it is explicitly specified in the query expression.<\/p>\n<p>On the other hand, a concatenated list is an ordered structure. Each element in the list has a specific position. In fact, concatenation itself is an order-utilizing operation in the sense that values can be prefixed or post fixed to an existing list. So approaches that are loosely called &#8220;concatenating row values&#8221;, &#8220;aggregate concatenation&#8221; etc. would have to make sure that some kind of an order, either explicit or implicit, should be specified prior to concatenating the row values. If such an ordering criteria is not provided, the concatenated string would be arbitrary in nature.<\/p>\n<h2 id=\"Toc205129482\">Considerations<\/h2>\n<p>Generally, requests for row value concatenations often comes in two basic flavors, when the number of rows is known and small (typically less than 10) and when the number of rows is unknown and potentially large. It may be better to look at each of them separately.<\/p>\n<p><strong>You may also be interested in:<\/strong> <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/the-poor-neglected-full-outer-join\/\" target=\"_blank\" rel=\"noopener\">Joining datasets with FULL OUTER JOIN before aggregation<\/a><\/p>\n<p>In some cases, all the programmer wants is just the list of values from a set of rows. There is no grouping or logical partitioning of values such as \u00a0the list of email addresses separated by a semicolon or some such. In such situations, the approaches can be the same except that the join conditions may vary. Minor variations of the examples list on this page illustrate such solutions as well.<\/p>\n<p>For the purpose of this article the Products table from Northwind database is used to illustrate column value concatenations with a grouping column. Northwind is a sample database in SQL Server 2000 default installations. You can download a copy from from the <a href=\"http:\/\/www.microsoft.com\/downloads\/details.aspx?familyid=06616212-0356-46a0-8da2-eebc53a68034&amp;displaylang=en\">Microsoft Downloads<\/a><\/p>\n<h2 id=\"Toc205129483\">Concatenating values when the number of items is small and known beforehand<\/h2>\n<p>When the number of rows is small and almost known beforehand, it is easier to generate the code. One common approach where there is a small set of finite rows is the pivoting method. Here is an example where only the first four alphabetically-sorted product names per <b>categoryid<\/b> is retrieved:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\u00a0 SELECT CategoryId,\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0MAX( CASE seq WHEN 1 THEN ProductName ELSE '' END ) + ', ' +\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MAX( CASE seq WHEN 2 THEN ProductName ELSE '' END ) + ', ' +\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MAX( CASE seq WHEN 3 THEN ProductName ELSE '' END ) + ', ' +\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MAX( CASE seq WHEN 4 THEN ProductName ELSE '' END )\n\u00a0\u00a0\u00a0\u00a0\u00a0 FROM ( SELECT p1.CategoryId, p1.ProductName,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( SELECT COUNT(*) \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Northwind.dbo.Products p2\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0WHERE p2.CategoryId = p1.CategoryId\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND p2.ProductName &lt;= p1.ProductName )\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM Northwind.dbo.Products p1 ) D ( CategoryId, ProductName, seq )\n\u00a0\u00a0\u00a0\u00a0 GROUP BY CategoryId ;\n<\/pre>\n<p>\u00a0The idea here is to create a expression inside the correlated subquery that produces a rank (<b>seq<\/b>) based on the product names and then use it in the outer query. Using common table expressions and the <b>ROW_NUMBER() <\/b>function, you can re-write this as:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">; WITH CTE ( CategoryId, ProductName, seq )\n\u00a0\u00a0\u00a0\u00a0 AS ( SELECT p1.CategoryId, p1.ProductName,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ROW_NUMBER() OVER ( PARTITION BY CategoryId ORDER BY ProductName )\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Northwind.dbo.Products p1 )\nSELECT CategoryId,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MAX( CASE seq WHEN 1 THEN ProductName ELSE '' END ) + ', ' +\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MAX( CASE seq WHEN 2 THEN ProductName ELSE '' END ) + ', ' +\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MAX( CASE seq WHEN 3 THEN ProductName ELSE '' END ) + ', ' +\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MAX( CASE seq WHEN 4 THEN ProductName ELSE '' END )\n\u00a0\u00a0\u00a0\u00a0\u00a0FROM CTE \n\u00a0\u00a0\u00a0\u00a0 GROUP BY CategoryId ;\n<\/pre>\n<p>\u00a0Note that <b>ROW_NUMBER() <\/b>is a newly-introduced feature in SQL 2005. If you are using any previous version, you will have to use the subquery approach (You can also use a self-join, to write it a bit differently). Using the recently introduced <b>PIVOT<\/b> operator, you can write this as follows:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT CategoryId, \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"1\" + ', ' + \"2\" + ', ' + \"3\" + ', ' + \"4\" AS Product_List\n\u00a0\u00a0\u00a0\u00a0\u00a0 FROM ( SELECT CategoryId, ProductName, \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ROW_NUMBER() OVER (PARTITION BY CategoryId \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER BY ProductName)\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Northwind.dbo.Products ) P ( CategoryId, ProductName, seq )\n\u00a0\u00a0\u00a0\u00a0 PIVOT ( MAX( ProductName ) FOR seq IN ( \"1\", \"2\", \"3\", \"4\" ) ) AS P_ ;\n<\/pre>\n<p>Not only does the syntax appear a bit confusing, but also it does not seem to offer any more functionality than the previous <b>CASE<\/b> approach. However, in rare situations, it could come in handy.<\/p>\n<h2 id=\"Toc205129484\">Concatenating values when the number of items is not known<\/h2>\n<p>When you do not know the number of items that are to be concatenated beforehand, the code can become rather \u00a0more demanding. The new features in SQL 2005 make some of the approaches easier. For instance, the recursive common table expressions (CTEs) and the <b>FOR XML PATH(&#8221;)<\/b> syntax makes the server do the hard work behind the concatenation, leaving the programmer to deal with the presentation issues. The examples below make this point obvious.<\/p>\n<h3><a id=\"Toc205129485\"><\/a>Recursive CTE methods<\/h3>\n<p>The idea behind this method is from a newsgroup posting by Vadim Tropashko. It is similar to the ideas behind generating a materialized path for hierarchies.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">WITH CTE ( CategoryId, product_list, product_name, length ) \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AS ( SELECT CategoryId, CAST( '' AS VARCHAR(8000) ), CAST( '' AS VARCHAR(8000) ), 0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Northwind..Products\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 GROUP BY CategoryId\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNION ALL\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT p.CategoryId, CAST( product_list + \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CASE WHEN length = 0 THEN '' ELSE ', ' END + ProductName AS VARCHAR(8000) ), \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CAST( ProductName AS VARCHAR(8000)), length + 1\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM CTE c\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN Northwind..Products p\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON c.CategoryId = p.CategoryId\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE p.ProductName &gt; c.product_name )\nSELECT CategoryId, product_list \n\u00a0\u00a0\u00a0\u00a0\u00a0 FROM ( SELECT CategoryId, product_list, \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RANK() OVER ( PARTITION BY CategoryId ORDER BY length DESC )\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM CTE ) D ( CategoryId, product_list, rank )\n\u00a0\u00a0\u00a0\u00a0 WHERE rank = 1 ;\n<\/pre>\n<p class=\"MsoNormal\">\u00a0The <b>CASE<\/b> in the recursive part of the CTE is used to eliminate the initial comma, but you can use <b>RIGHT<\/b> or the <b>SUBSTRING<\/b> functions instead. This may not be the best performing option, but certain additional tuning could be done to make them suitable for medium sized datasets.<\/p>\n<p class=\"MsoNormal\">Another approach using recursive common table expressions was sent in by Anub Philip, an Engineer from Sathyam Computers that uses separate common table expressions for the anchor and recursive parts.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">WITH Ranked ( CategoryId, rnk, ProductName )\u00a0 \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AS ( SELECT CategoryId,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ROW_NUMBER() OVER( PARTITION BY CategoryId ORDER BY CategoryId ),\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CAST( ProductName AS VARCHAR(8000) ) \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Northwind..Products),\n\u00a0\u00a0 AnchorRanked ( CategoryId, rnk, ProductName )\u00a0 \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AS ( SELECT CategoryId, rnk, ProductName \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Ranked\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE rnk = 1 ),\nRecurRanked ( CategoryId, rnk, ProductName ) \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AS ( SELECT CategoryId, rnk, ProductName \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM AnchorRanked\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNION ALL \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT Ranked.CategoryId, Ranked.rnk,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RecurRanked.ProductName + ', ' + Ranked.ProductName\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Ranked\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN RecurRanked \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON Ranked.CategoryId = RecurRanked.CategoryId \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND Ranked.rnk = RecurRanked.rnk + 1 )\nSELECT CategoryId, MAX( ProductName ) \n\u00a0\u00a0\u00a0\u00a0\u00a0 FROM RecurRanked\n\u00a0 GROUP BY CategoryId;\n<\/pre>\n<p>On first glance, this query may seem a bit expensive in comparison, but the reader is encouraged to check the execution plans and make any additional tweaks as needed.<\/p>\n<h3>The blackbox XML methods<\/h3>\n<p>Here is a technique for string concatenation that uses the <b>FOR XML<\/b> clause with <b>PATH<\/b> mode. It was initially posted by Eugene Kogan, and later became common in public newsgroups.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT p1.CategoryId,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( SELECT ProductName + ',' \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Northwind.dbo.Products p2\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0WHERE p2.CategoryId = p1.CategoryId\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0ORDER BY ProductName\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FOR XML PATH('') ) AS Products\n\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Northwind.dbo.Products p1\n\u00a0\u00a0\u00a0\u00a0 \u00a0GROUP BY CategoryId ;\n\u00a0\n<\/pre>\n<p class=\"MsoNormal\">There is a similar approach that was originally found in the beta newsgroups, using the <b>CROSS APPLY<\/b> operator.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT DISTINCT CategoryId, ProductNames\n\u00a0\u00a0\u00a0 FROM Northwind.dbo.Products p1\n\u00a0\u00a0 CROSS APPLY ( SELECT ProductName + ',' \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Northwind.dbo.Products p2\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0 WHERE p2.CategoryId = p1.CategoryId \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0ORDER BY ProductName \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FOR XML PATH('') )\u00a0 D ( ProductNames )\n<\/pre>\n<p>You may notice a comma at the end of the concatenated string, which you can remove using a <b>STUFF, SUBSTRING<\/b> or <b>LEFT<\/b> function. While the above methods are deemed reliable by many at the time of writing, there is no guarantee that it will stay that way, given that the internal workings and evaluation rules of <b>FOR XML PATH()<\/b> expression in correlated subqueries are not well documented.<\/p>\n<p>The problem with this approach is that the contents of the ProductName column is interpreted a XML rather than text, which will lead to\u00a0 certain characters being &#8216;entitized&#8217;, or in some cases, leading to the SQL causing an error. (see note below, and solution by Adam Machanic in comments below) and to avoid this, it is better to use a slightly revised syntax like this..<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT p1.CategoryId,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 stuff( (SELECT ','+ProductName \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Northwind.dbo.Products p2\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE p2.CategoryId = p1.CategoryId\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER BY ProductName\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FOR XML PATH(''), TYPE).value('.', 'varchar(max)')\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,1,1,'')\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AS Products\n\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Northwind.dbo.Products p1\n\u00a0\u00a0\u00a0\u00a0\u00a0 GROUP BY CategoryId ;\n<\/pre>\n<p>&#8230;and this&#8230;<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT DISTINCT CategoryId, ProductNames\n\u00a0\u00a0\u00a0 FROM Northwind.dbo.Products p1\n\u00a0\u00a0 CROSS APPLY ( SELECT \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 stuff( (SELECT ','+ProductName \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Northwind.dbo.Products p2\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE p2.CategoryId = p1.CategoryId\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER BY ProductName\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FOR XML PATH(''), TYPE).value('.', 'varchar(max)')\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,1,1,'')\n\u00a0\u00a0\u00a0 )\u00a0 D ( ProductNames )\n\n<\/pre>\n<h3>Using Common Language Runtime<\/h3>\n<p>Though this article is about approaches using Transact SQL, this section is included due to the popularity of <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/clr-assembly-regex-functions-for-sql-server-by-example\/\" target=\"_blank\" rel=\"noopener\">CLR aggregates<\/a> in SQL 2005. It not only empowers the CLR programmer with new options for database development, but also, in some cases, they work at least as well as native Transact SQL approaches.<\/p>\n<p>If you are familiar with .NET languages, SQL 2005 offers a convenient way to create user defined aggregate functions using C#, VB.NET or similar languages that are supported by the Common Language Runtime (CLR). Here is an example of a string concatenate aggregate function written using C#.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">using System;\nusing System.Collections.Generic;\nusing System.Data.SqlTypes;\nusing System.IO;\nusing Microsoft.SqlServer.Server;\n\u00a0\n[Serializable]\n[SqlUserDefinedAggregate(Format.UserDefined,\u00a0 MaxByteSize=8000)]\npublic struct strconcat : IBinarySerialize{\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 private List values;\n\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public void Init()\u00a0\u00a0\u00a0 {\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 this.values = new List();\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\n\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public void Accumulate(SqlString value)\u00a0\u00a0\u00a0 {\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 this.values.Add(value.Value);\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\n\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public void Merge(strconcat value)\u00a0\u00a0\u00a0 {\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 this.values.AddRange(value.values.ToArray());\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\n\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public SqlString Terminate()\u00a0\u00a0\u00a0 {\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return new SqlString(string.Join(\", \", this.values.ToArray()));\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\n\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public void Read(BinaryReader r)\u00a0\u00a0\u00a0 {\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 int itemCount = r.ReadInt32();\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 this.values = new List(itemCount);\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 for (int i = 0; i &lt;= itemCount - 1; i++)\u00a0\u00a0\u00a0 {\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 this.values.Add(r.ReadString());\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\n\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public void Write(BinaryWriter w)\u00a0\u00a0\u00a0 {\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 w.Write(this.values.Count);\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 foreach (string s in this.values)\u00a0\u00a0\u00a0\u00a0\u00a0 {\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 w.Write(s);\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }\n}\n<\/pre>\n<p>Once you build and deploy this assembly on the server, you should be able to execute your concatenation query as:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT CategoryId, \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbo.strconcat(ProductName) \n\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Products \n\u00a0\u00a0\u00a0\u00a0 GROUP BY CategoryId ; \n<\/pre>\n<p>\u00a0If you are a total newbie on CLR languages, and would like to learn more about developing database solutions using CLR languages, consider starting at <a href=\"http:\/\/msdn2.microsoft.com\/en-us\/library\/ms131089.aspx\">Introduction to Common Language Runtime (CLR) Integration<\/a><\/p>\n<h3>Scalar UDF with recursion<\/h3>\n<p>Recursive functions in t-SQL have a drawback that the maximum nesting level is 32. So this approach is applicable only for smaller datasets, especially when the number of items within a group, that needs to be concatenated, is less than 32.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE FUNCTION udf_recursive ( @cid INT, @i INT ) \nRETURNS VARCHAR(8000) AS BEGIN \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DECLARE @r VARCHAR(8000), @l VARCHAR(8000) \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @i = @i - 1,\u00a0 @r = ProductName + ', ' \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Northwind..Products p1 \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE CategoryId = @cid \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND @i = ( SELECT COUNT(*) FROM Northwind..Products p2 \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE p2.CategoryId = p1.CategoryId \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND p2.ProductName &lt;= p1.ProductName ) ; \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IF @i &gt; 0 BEGIN \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 EXEC @l = dbo.udf_recursive @cid, @i ;\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SET @r =\u00a0 @l + @r ;\nEND \nRETURN @r ;\nEND \n<\/pre>\n<p>This function can be invoked as follows:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT CategoryId, \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 dbo.udf_recursive( CategoryId, COUNT(ProductName) ) \n\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Northwind..Products \n\u00a0\u00a0\u00a0\u00a0 GROUP BY CategoryId ; \n<\/pre>\n<h3>Table valued UDF with a WHILE loop<\/h3>\n<p>This approach is based on the idea by Linda Wierzbecki where a table variable with three columns is used within a table-valued UDF. The first column represents the group, second represents the currently processing value within a group and the third represents the concatenated list of values.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE FUNCTION udf_tbl_Concat() RETURNS @t TABLE(\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CategoryId INT, \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Product VARCHAR(40), \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 list VARCHAR(8000) ) \nBEGIN \n\u00a0\u00a0\u00a0\u00a0 INSERT @t (CategoryId, Product, list) \n\u00a0\u00a0\u00a0\u00a0 SELECT CategoryId, MIN(ProductName),\u00a0 MIN(ProductName) \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Products \n\u00a0\u00a0\u00a0\u00a0\u00a0 GROUP BY CategoryId \nWHILE ( SELECT COUNT(Product) FROM @t ) &gt; 0 BEGIN \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UPDATE t \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SET list = list + COALESCE(\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( SELECT ', ' + MIN( ProductName ) \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Northwind..Products \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE Products.CategoryId = t.CategoryId \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND Products.ProductName &gt; t.Product), ''), \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Product = ( SELECT MIN(ProductName) \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Northwind..Products \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE Products.CategoryId = t.CategoryId \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND Products.ProductName &gt; t.Product ) \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM @t t END \nRETURN \nEND \n\u00a0\n<\/pre>\n<p>\u00a0The usage of the above function can be like:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT CategoryId, list AS Products\n\u00a0 FROM udf_tbl_Concat() ; \n<\/pre>\n<h3>\u00a0<a id=\"Toc205129490\"><\/a>Dynamic SQL<\/h3>\n<p>This approach is a variation of the kludge often known using the nickname of \u00a0&#8216;dynamic cross tabulation&#8217;. There is enough literature out there which demonstrates the drawbacks and implications of using Dynamic SQL. A popular one, at least from Transact SQL programmer&#8217;s perspective, is Erland&#8217;s <a href=\"http:\/\/www.sommarskog.se\/dynamic_sql.html\">Curse and Blessings of Dynamic SQL.<\/a> The Dynamic SQL approaches can be developed based on creating a Transact SQL query string based on the number of groups and then use a series of CASE expressions or ROW_NUMBER() function to pivot the data for concatenation.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">DECLARE @r VARCHAR(MAX), @n INT, @i INT \nSELECT @i = 1,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @r = 'SELECT CategoryId, ' + CHAR(13), \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0@n = (SELECT TOP 1 COUNT( ProductName ) \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Northwind..Products \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 GROUP BY CategoryId \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER BY COUNT( ProductName ) DESC ) ;\u00a0\u00a0\u00a0\u00a0\u00a0 \nWHILE @i &lt;= @n BEGIN \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 SET @r = @r + \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 CASE WHEN @i = 1\u00a0 \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN 'MAX( CASE Seq WHEN ' + CAST( @i AS VARCHAR ) + ' \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN ProductName \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE SPACE(0) END ) + ' + CHAR(13) \n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0WHEN @i = @n \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN 'MAX( CASE Seq WHEN ' + CAST( @i AS VARCHAR ) + ' \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN '', '' + ProductName \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE SPACE(0) END ) ' + CHAR(13) \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE 'MAX( CASE Seq WHEN ' + CAST( @i AS VARCHAR ) + ' \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0THEN '', '' + ProductName \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE SPACE(0) END ) + ' + CHAR(13)\u00a0 \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 END ;\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 SET @i = @i + 1 ;\nEND \nSET @r = @r + ' \n\u00a0\u00a0\u00a0 FROM ( SELECT CategoryId, ProductName, \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ROW_NUMBER() OVER ( PARTITION BY CategoryId ORDER BY ProductName )\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Northwind..Products p ) D ( CategoryId, ProductName, Seq ) \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 GROUP BY CategoryId;' \nEXEC( @r ) ;\n<\/pre>\n<h3>\u00a0<a id=\"Toc205129491\"><\/a>The Cursor approach<\/h3>\n<p>The drawbacks of rampant usage of cursors are well-known among the Transact SQL community. Because they are generally resource intensive, procedural and inefficient, one should strive to avoid cursors or loop based solutions in general Transact SQL programming.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">DECLARE @tbl TABLE (id INT PRIMARY KEY, list VARCHAR(8000)) \nSET NOCOUNT ON \nDECLARE @c INT, @p VARCHAR(8000), @cNext INT, @pNext VARCHAR(40) \nDECLARE c CURSOR FOR \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT CategoryId, ProductName \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Northwind..Products \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER BY CategoryId, ProductName ;\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OPEN c ;\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FETCH NEXT FROM c INTO @cNext, @pNext ;\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SET @c = @cNext ;\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHILE @@FETCH_STATUS = 0 BEGIN \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IF @cNext &gt; @c BEGIN \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INSERT @tbl SELECT @c, @p ;\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @p = @PNext, @c = @cNext ;\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END ELSE \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SET @p = COALESCE(@p + ',', SPACE(0)) + @pNext ;\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FETCH NEXT FROM c INTO @cNext, @pNext \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INSERT @tbl SELECT @c, @p ;\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CLOSE c ;\nDEALLOCATE c ;\nSELECT * FROM @tbl ;\n\u00a0\n<\/pre>\n<h2>\u00a0<a id=\"Toc205129492\"><\/a>Unreliable approaches<\/h2>\n<p>This section details a couple of notorious methods often publicized by some in public forums. The problem with these methods is that they rely on the physical implementation model; changes in indexes, statistics etc or even a change of a simple expression in the SELECT list or ORDER BY clause can change the output. Also these are undocumented, unsupported and unreliable to the point where one can consistently demonstrate failures. Therefore these methods are not recommended at all for production mode systems.<\/p>\n<h3>Scalar UDF with t-SQL update extension<\/h3>\n<p>It is rare for the usage of an expression that involves a column, a variable and an expression in the SET clause in an UPDATE statement to appear intuitive. However, in general, the optimizer often seems to process these values in the order of materialization, either in the internal work tables or any other storage structures.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE FUNCTION udf_update_concat (@CategoryId INT) \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RETURNS VARCHAR(MAX) AS \nBEGIN \nDECLARE @t TABLE(p VARCHAR(40));\nDECLARE @r VARCHAR(MAX) ;\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SET @r = SPACE(0) ;\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INSERT @t ( p ) SELECT ProductName FROM Northwind..Products \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE CategoryId = @CategoryId ;\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IF @@ROWCOUNT &gt; 0 \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UPDATE @t \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SET @r = @r + p + ',' ;\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RETURN(@r) \nEND \n<\/pre>\n<p>Here is how to use this function:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT CategoryId, dbo.udf_update_concat(CategoryId) \n\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Northwind..Products \n\u00a0\u00a0\u00a0\u00a0 GROUP BY CategoryId ; \n<\/pre>\n<p>Again, it is important to consider that lack of physical independence that is being exploited here before using or recommending this as a usable and meaningful solution.<\/p>\n<h3>Scalar UDF with variable concatenation in SELECT<\/h3>\n<p>This is an approach purely dependent on the physical implementation and internal access paths. Before using this approach, make sure to refer to the <a href=\"http:\/\/support.microsoft.com\/default.aspx\/kb\/287515\">relevant knowledgebase article<\/a>.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE FUNCTION dbo.udf_select_concat ( @c INT )\nRETURNS VARCHAR(MAX) AS BEGIN\nDECLARE @p VARCHAR(MAX) ;\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SET @p = '' ;\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT @p = @p + ProductName + ','\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Northwind..Products\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE CategoryId = @c ;\nRETURN @p\nEND\n<\/pre>\n<p class=\"MsoNormal\">And, as for its usage:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT CategoryId, dbo.udf_select_concat( CategoryId )\n\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Northwind..Products\n\u00a0\u00a0\u00a0\u00a0 GROUP BY CategoryId ;\n<\/pre>\n<h2>Conclusion<\/h2>\n<p>Regardless of how it is used, &#8220;aggregate concatenation&#8221; of row values in Transact SQL, especially when there is a grouping, is not a simple routine.<\/p>\n<p><strong>You may also be interested in:<\/strong> <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/producing-json-documents-from-sql-server-queries-via-tsql\/\" target=\"_blank\" rel=\"noopener\">Producing JSON documents from SQL Server queries<\/a><\/p>\n<p>You need to consider carefully the circumstances\u00a0 before you choose one method over another.\u00a0 The most logical choice would to have a built-in operator with optional configurable parameters that can do the concatenation of the values depending on the type. Till then, reporting requirements and external data export routines will have to rely on such Transact SQL programming hacks.<\/p>\n<h2>References<\/h2>\n<ul>\n<li><a href=\"http:\/\/support.microsoft.com\/default.aspx\/kb\/287515\">PRB: Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location<\/a><\/li>\n<li><a href=\"http:\/\/www.microsoft.com\/downloads\/details.aspx?familyid=06616212-0356-46a0-8da2-eebc53a68034&amp;displaylang=en\">Northwind and pubs Sample Databases for SQL Server 2000<\/a><\/li>\n<li><a href=\"http:\/\/www.sommarskog.se\/dynamic_sql.html\">The Curse and Blessings of Dynamic SQL<\/a><\/li>\n<li><a href=\"http:\/\/msdn2.microsoft.com\/en-us\/library\/ms131089.aspx\">Introduction to Common Language Runtime (CLR) Integration<\/a><\/li>\n<\/ul>\n<h2>Acknowledgements<\/h2>\n<p>Umachandar Jayachandran, Linda Wierzbecki, Bruce Margolin, Roy Harvey, Eugene Kogan, Vadim Tropashko, Anub Philip.<\/p>\n<div class=\"note\">\n<p class=\"note\">Additional information about the XML Blackbox approach inserted 22\/03\/2012.<\/p>\n<\/div>\n<\/div>\n\n\n\n<section id=\"faq\" class=\"faq-block my-5xl\">\n    <h2>FAQs: Concatenating Row Values in Transact SQL<\/h2>\n\n                        <h3 class=\"mt-4xl\">1. What is the best way to concatenate rows in SQL Server?<\/h3>\n            <div class=\"faq-answer\">\n                <p>If you are on SQL Server 2017 or later, use STRING_AGG(column, delimiter). It is built in, supports WITHIN GROUP ordering, and handles NULLs cleanly. For SQL Server 2016 and earlier, the FOR XML PATH(&#8221;) technique is the most widely used and reliable alternative. Avoid cursor-based approaches and the SET variable trick &#8211; both are fragile under query plan changes.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">2. How does STRING_AGG work in SQL Server?<\/h3>\n            <div class=\"faq-answer\">\n                <p>STRING_AGG(expression, separator) concatenates non-NULL values from multiple rows into a single string using the specified separator. It works like an aggregate function: combine it with GROUP BY to produce one concatenated string per group. Use WITHIN GROUP (ORDER BY column) to control the order of values in the output string.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">3. How do I concatenate rows in SQL Server 2014 or 2016?<\/h3>\n            <div class=\"faq-answer\">\n                <p>Use the FOR XML PATH(&#8221;) technique: SELECT STUFF((SELECT &#8216;,&#8217; + column FROM table WHERE group_col = t.group_col FOR XML PATH(&#8221;), TYPE).value(&#8216;.&#8217;, &#8216;NVARCHAR(MAX)&#8217;), 1, 1, &#8221;) FROM table t GROUP BY group_col. The STUFF removes the leading delimiter, and the .value() call handles XML character escaping.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">4. Does STRING_AGG remove duplicate values?<\/h3>\n            <div class=\"faq-answer\">\n                <p>Not by default. STRING_AGG concatenates all values including duplicates. To deduplicate, you cannot use DISTINCT directly inside STRING_AGG in all SQL Server versions &#8211; instead, pre-aggregate your values in a subquery or CTE and use DISTINCT there, then feed the deduplicated set into STRING_AGG.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"<p>Learn how to concatenate row values in SQL Server using STRING_AGG (SQL Server 2017+), FOR XML PATH, recursive CTEs, and CLR aggregates. Practical T-SQL examples for both known and unknown row counts, with guidance on which method to use when.&hellip;<\/p>\n","protected":false},"author":10401,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4150,5134,4183,4252],"coauthors":[6794],"class_list":["post-410","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-sql","tag-sql-prompt","tag-t-sql","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/410","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/10401"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=410"}],"version-history":[{"count":8,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/410\/revisions"}],"predecessor-version":[{"id":110012,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/410\/revisions\/110012"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=410"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=410"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=410"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=410"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}