Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

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

, 20 Nov 2011
Rate this:
Please Sign up or sign in to vote.
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:
 
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)

Share

About the Author

Jovan Popovic
Architect Gowi
Serbia Serbia
Started as a young scientist - winning the highest national awards in mathematics, physics, electrotechnics, and electronics.
Graduated from Faculty of Electrical Engineering, Department of Computer Techniques and Informatics, University of Belgrade, Serbia, as a first in the class, as a Master of Software Sciences.
Currently working in Gowi as a Software engineer, architect, and project manager since 2004 - mostly using Microsoft technologies (ASP.NET, C#). Member of JQuery community - created few popular plugins (four popular JQuery DataTables add-ins and loadJSON template engine).
Interests: Software engineering process(estimation and standardization), mobile and business intelligence platforms.

Comments and Discussions

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

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140926.1 | Last Updated 20 Nov 2011
Article Copyright 2011 by Jovan Popovic
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid