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

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

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

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

SELECT parent.id AS ID, child1.value AS Value1, child2.value AS Value2 FROM parent LEFT JOIN child AS child1 ON parent.id = child1.parent_id AND child1.field_id = 1 LEFT JOIN child AS child2 ON parent.id = child2.parent_id AND child2.field_id = 2 WHERE parent.id = child1.parent_id AND parent.id = child2.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: