Click here to Skip to main content
15,867,453 members
Articles / Programming Languages / SQL
Tip/Trick

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

Rate me:
Please Sign up or sign in to vote.
4.71/5 (6 votes)
20 Nov 2011CPOL1 min read 31.7K   5   6
This SQL example shows how you can return parent row all child rows as a single 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:

SQL
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Program Manager Microsoft
Serbia Serbia
Graduated from Faculty of Electrical Engineering, Department of Computer Techniques and Informatics, University of Belgrade, Serbia.
Currently working in Microsoft as Program Manager on SQL Server product.
Member of JQuery community - created few popular plugins (four popular JQuery DataTables add-ins and loadJSON template engine).
Interests: Web and databases, Software engineering process(estimation and standardization), mobile and business intelligence platforms.

Comments and Discussions

 
QuestionNice! Pin
Jon_Boy24-Jul-13 2:14
Jon_Boy24-Jul-13 2:14 
Generalhow do i get distinct value in a list from child table. eg, ... Pin
manish_sapkal9-Dec-11 0:35
manish_sapkal9-Dec-11 0:35 
GeneralYes but it only trims spaces, not commas. Pin
Steve Roberson22-Nov-11 5:43
Steve Roberson22-Nov-11 5:43 
GeneralI believe the rtrim function that will trim characters more ... Pin
Steve Roberson21-Nov-11 11:15
Steve Roberson21-Nov-11 11:15 
GeneralRe: It is supported in MS SQL too - see http://msdn.microsoft.co... Pin
Jovan Popovic(MSFT)21-Nov-11 22:26
Jovan Popovic(MSFT)21-Nov-11 22:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.