Formatting child table rows as a list in a single column cell

Introduction

Imagine this kind of SQL query - you have parent/child tables such as Product/ProductItem, Company/Employee, Article/Categories where parent and child tables are connected with one-to-many relationship. Let we assume that you have the following Product/ProductCategory tables with the following structure:

Product
+ProductId
+ProductName

ProductCategory
+ProductCategoryId
+ProductId
+ProductCategoryName

Each product can have several product categories which are connected via ProductId foreign key.

Problem

How can you create SQL query that returns parent row and all child rows in the single cell (e.g. as comma separated values). Example of such kind of result would be:

Products | ProductCategoryList
-----------------------------------------
ProductA | Software,C#,Windows,
ProductB | Software,Java,Web,Ajax,
ProductC | Hardware,Computer,Electronic,

Solution

You can use nested query in the child column where you can take all related child records filtered by the parent id. In order to return these records as a single value, you can use FOR XML clause as shown in the following listing:

SELECT Products, (   SELECT ProductCategoryName + ','
                        FROM ProductCategory
                        WHERE ProductCategory.ProductId = Product.ProductId
                        FOR XML PATH('')) AS ProductCategoryList
FROM Product

In this code example, I have selected all product categories related to the current Product row using the ProductId, added one comma to each ProductCategoryName, and formatted results as XML elements using the FOR XML clause. Name of the XML element that will wrap each product category name can be set using the PATH(ELEMENT_NAME) part. In this case, I have placed empty string for the Xml element name so no Xml tags are placed in the output.
Note that this query returns trailing "," after the last category. If you don't like it you can use RTRIM(',') to remove it.

If you have a similar SQL structure in format PARENTTABLE(ParentId,ParentName) and CHILDTABLE(ChildId, ParentId, ChildName), you can easily apply this script to return set of child names for each parent row.