Break string in a record into new record mysql

Question

In my database, I have to select a query like this.

mysql> SELECT
    -> @rownum := @rownum + 1 as id,
    -> GROUP_CONCAT(DISTINCT id SEPARATOR ", ") as container_ids
    ->
    -> FROM barang
    -> CROSS JOIN (SELECT @rownum := 0) r
    ->
    -> GROUP BY barang.arrival_day, barang.carrier
    -> ORDER BY barang.arrival_day ASC
    ->
    -> LIMIT 5;
+-----------+---------------------------------------+
| item_id   | container_ids                         |
+-----------+---------------------------------------+
|    1      | 1, 2                                  |
|    2      | 3                                     |
|    3      | 4                                     |
|    4      | 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15 |
|    5      | 16, 17                                |
+-----------+---------------------------------------+
5 rows in set (0.00 sec)

My goal is to manipulate the query again so it will be get the result like this,

+-----------+----------------------------------------+
| item_id   | container_ids                          |
+-----------+----------------------------------------+
|    1      | 1                                      |
|    1      | 2                                      |
|    2      | 3                                      |
|    3      | 4                                      |
|    4      | 5                                      |
|    4      | 6                                      |
|    4      | 7                                      |
|    4      | 8                                      |
|    4      | 9                                      |
|    4      | 10                                     |
|    4      | 11                                     |
|    4      | 12                                     |
|    4      | 13                                     |
|    4      | 14                                     |
|    4      | 15                                     |
|    5      | 16                                     |
|    5      | 17                                     |
+-----------+---------------------------------------+

What keyword if I have a case like this. Please advise.

The values only need to be split because they were `group_concat`-ed in the first place. Maybe there is a way of writing this query *without* the `group_concat` clause? Can you provide some sample input data? — Carsten Massmann, Aug 03 '18 at 17:59

score 0 · Answer 1 · answered Aug 03 '18 at 17:41

There is no real easy way to split a list of values in SQL. You could omit the GROUP_CONCAT and get similar results, but the LIMIT would have to be different to get the same results. The easiest might be to change the how the @rownum increments, so that it only increments on changes to previous (barang.arrival_day, barang.carrier) values and filter results on @rownum <= 5.

Break string in a record into new record mysql

1 Answers1