Imagine this kind of SQL query - you have parent/child tables such as
where parent and child tables are connected with one-to-many relationship. Let we assume that you have the following
tables with the following structure:
Each product can have several product categories which are connected via
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,
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
clause as shown in the following listing:
SELECT Products, ( SELECT ProductCategoryName + ','
WHERE ProductCategory.ProductId = Product.ProductId
FOR XML PATH('')) AS ProductCategoryList
In this code example, I have selected all product categories related to the current Product row using the
, added one comma to each
, 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
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
CHILDTABLE(ChildId, ParentId, ChildName)
, you can easily apply this script to return set of child names for each parent row.