Concatenation of rows in T-SQL

Question

I use SQL Server 2008 and I have to three tables, products, producttags and tags.

Products
-----------------
Id | Name

ProductTags 
------------------
Id | ProductId | TagId

Tags
------------------
Id | Name

I'm trying to create query that returns a result that contains the product id in the first column and the name of the tags associated with the product concatenated in the second column, like this:

productid | Tags
-------------------------------------
1           tag1, tag2, tag3
2           tag2, tag3

I know this can be accomplished with FOR XML PATH('') in some way, but I just can get it right. Using FOR XML is not important. Any solution that will produce the result will do.

There are literally dozens similar questions on SO: http://stackoverflow.com/questions/1874966/concatenate-row-values-t-sql http://stackoverflow.com/questions/5031204/does-t-sql-have-an-aggregate-function-to-concatenate-strings http://stackoverflow.com/questions/3087684/sql-query-to-return-values-of-a-particular-column-concactenated-with-comma http://stackoverflow.com/questions/1075307/sql-server-row-concatenation http://stackoverflow.com/questions/3197626/concatenate-multiple-rows — Andrew Savinykh, Mar 08 '13 at 09:16
possible duplicate of [Combine multiple results in a subquery into a single comma-separated value](http://stackoverflow.com/questions/111341/combine-multiple-results-in-a-subquery-into-a-single-comma-separated-value) — Jonathan Leffler, Apr 18 '13 at 00:52
[This article](http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/) provides a good overview of methods you can achieve this with. — Andrew Savinykh, Mar 08 '13 at 09:11

score 2 · Accepted Answer · answered Mar 08 '13 at 09:13

SELECT
     c.ID, c.Name ProductName,
     STUFF(
         (SELECT    ',' + b.name
          FROM  ProductTags a
                INNER JOIN Tags b
            ON a.TagID = b.ID
          WHERE  a.ProductID = c.ID
          FOR XML PATH (''))
          , 1, 1, '')  AS TagListList
FROM Products AS c
GROUP BY c.Name, c.ID

SQLFiddle Demo

Concatenation of rows in T-SQL

1 Answers1