SQL: Multiple child rows in one row

Lets say you have two tables with 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 a 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:

If you need a different seperator 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;
  

If you want to get the list of parent along with all child values in a single row but multiple columns, you can run a 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: