T-SQL String Concatenating multiple rows

Question

Possible Duplicates:
Simulating group_concat MySQL function in MS SQL Server 2005?
Concatenate values based on ID

I am trying to write a SQL query in SSMS 2008 to group by a single column. I have a table with just two columns: Part Number and Product Code. Both of these columns are VARCHAR data types.

Right now my table looks something like:

Part Number              Product Code
111222                   AAAAA
111222                   BBBBB
111222                   CCCCC
111223                   AAAAA
111224                   AAAAA
111225                   AAAAA
111226                   DDDDD
111226                   EEEEE

But I want this table to instead look like:

111222                   AAAAA, BBBBB, CCCCC                 
111223                   AAAAA
111224                   AAAAA
111225                   AAAAA
111226                   DDDDD, EEEEE

How do I do this? I have read some websites recommending coalesce function, but I can't seem to figure out a way to get this to work correctly.

Code Magician · Answer 1 · 2013-01-12T01:04:26.380

several approaches here http://web.archive.org/web/20120429052520/http://www.projectdmx.com/tsql/rowconcatenate.aspx

The article details far too many approaches to list in this answer however my favorite approach is this one:

The blackbox XML methods

An example for string concatenation using FOR XML clause with PATH mode is detailed below. It was initially posted by Eugene Kogan later became common in public newsgroups.

SELECT p1.CategoryId,
      ( SELECT ProductName + ',' 
          FROM Northwind.dbo.Products p2
         WHERE p2.CategoryId = p1.CategoryId
         ORDER BY ProductName
           FOR XML PATH('') ) AS Products
  FROM Northwind.dbo.Products p1
 GROUP BY CategoryId ;

Again, the similar approach originally found in the beta newsgroups, using CROSS APPLY operator.

SELECT DISTINCT CategoryId, ProductNames
  FROM Products p1
 CROSS APPLY ( SELECT ProductName + ',' 
                 FROM Products p2
                WHERE p2.CategoryId = p1.CategoryId 
                ORDER BY ProductName 
                  FOR XML PATH('') )  D ( ProductNames )

You may notice a comma at the end of the concatenated string, which you can remove using a STUFF, SUBSTRING or LEFT 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 the internal workings and evaluation rules of FOR XML PATH() expression in correlated subqueries are not well documented.

@bgolson changed the link to show source info and a quick except. In these situations in the past I've been discouraged by mods from copying the article. — Code Magician, Jan 12 '13 at 01:06
@M_M Thanks so much for the update! Probably not good to copy directly, but maybe apply what you found there and use it your own solution. — bgolson, Jan 12 '13 at 04:52

score 1 · Answer 2 · edited Jul 25 '13 at 20:14

1

You could do this:

DECLARE @ProductCodeCSV as varchar(max)

select @ProductCodeCSV = COALESCE(@ProductCodeCSV + ', ', '') + [product_code]
from table
where part_ = 34175--@ID

select @ProductCodeCSV

edited Jul 25 '13 at 20:14

Jerome

2,059
1
16
19

answered Aug 09 '11 at 23:59

Chains

12,541
8
45
62

T-SQL String Concatenating multiple rows

2 Answers2