CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Pelle
Pelle

Reputation: 2785

Concatenation of rows in T-SQL

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.

Upvotes: 0

Views: 191

Answers (1)

John Woo
John Woo

Reputation: 263893

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

Upvotes: 2

Related Questions