Combine Multiple child rows into one row MYSQL

Question

I have two tables Ordered_Item ``` ID | Item_Name 1 | Pizza 2 | Stromboli ``` Ordered_Options ``` Ordered_Item_ID | Option_Number | Value 1 43 Pepperoni 1 44 Extra Cheese 2 44 Extra Cheese ``` What I am looking to output is a mysql query is something to this effect Output ``` ID | Item_Name | Option_1 | Option_2 1 Pizza Pepperoni Extra Cheese 2 Stromboli NULL Extra Cheese ``` I have tried numerous options most ending in syntax error, I have tried group_concat but thats not really what I am looking for. I have a crude example below of what I think might be a start. I need the options to be in the same order every time. And in the program where the info is collected there is no way to reliable ensure that will happen. Is it possible to have them concatenate according to option number. Also I know that I will never have over 5 options so a static solution would work ``` Select Ordered_Items.ID, Ordered_Items.Item_Name, FROM Ordered_Items JOIN (SELECT Ordered_Options.Value FROM Ordered_Op…

Accepted Answer

The easiest way would be to make use of the GROUP_CONCAT group function here..

```
select
  ordered_item.id as `Id`,
  ordered_item.Item_Name as `ItemName`,
  GROUP_CONCAT(Ordered_Options.Value) as `Options`
from
  ordered_item,
  ordered_options
where
  ordered_item.id=ordered_options.ordered_item_id
group by
  ordered_item.id
```

Which would output:

```
Id              ItemName       Options

1               Pizza          Pepperoni,Extra Cheese

2               Stromboli      Extra Cheese
```

That way you can have as many options as you want without having to modify your query.

Ah, if you see your results getting cropped, you can increase the size limit of GROUP_CONCAT like this:

```
SET SESSION group_concat_max_len = 8192;
```

Combine Multiple child rows into one row MYSQL

Solution

Problem

Related problems