SQL Tricks – Multiple child rows in one row (Single column)

Let’s say you have two tables with the table structure as follows:

PARENT: id (PK), name
CHILD: id(PK), parent_id(FK), value

If you want to get the list of parent along with all child values in a single row, you can run an SQL query as follows:

SELECT parent.id AS ID, parent.name AS Name, GROUP_CONCAT(child.value) AS Values FROM parent, child WHERE parent.id = child.parent_id GROUP BY parent.id;

This will give you the list of all parents with all their child elements in one row. The output will look something like this:

If you need a different separator other than comma (","), you can modify the query as follows:

SELECT parent.id AS ID, parent.name AS Name, GROUP_CONCAT(child.value SEPARATOR '-') AS Values FROM parent, child WHERE parent.id = child.parent_id GROUP BY parent.id;