[SOLVED] how to split single row to multiple rows in mysql

Issue

I want to split each single row to two rows in mysql (I want to split num1 and num2 into two rows by comma). My data is like:

datetime1               count    num1    num2
2022-03-16 03:00:00     0        0,1     1,2
2022-03-16 04:00:00     0        0,1     1,2

and now I want data like this:

datetime1                count    num1 num2
2022-03-16 03:00:00      0        0    1
2022-03-16 03:00:00      0        0    2
2022-03-16 03:00:00      0        1    1
2022-03-16 03:00:00      0        1    2
2022-03-16 04:00:00      0        0    1
2022-03-16 04:00:00      0        0    2
2022-03-16 04:00:00      0        1    1
2022-03-16 04:00:00      0        1    2

Solution

We can use a cross/inner join approach here with the help of SUBSTRING_INDEX():

SELECT
    t1.datetime1,
    t1.count,
    t1.num1,
    t2.num2
FROM
(
    SELECT datetime1, count, SUBSTRING_INDEX(num1, ',', 1) AS num1
    FROM yourTable
    UNION ALL
    SELECT datetime1, count, SUBSTRING_INDEX(num1, ',', -1)
    FROM yourTable
) t1
INNER JOIN
(
    SELECT datetime1, count, SUBSTRING_INDEX(num2, ',', 1) AS num2
    FROM yourTable
    UNION ALL
    SELECT datetime1, count, SUBSTRING_INDEX(num2, ',', -1)
    FROM yourTable
) t2
    ON t2.datetime1 = t1.datetime1
ORDER BY
    t1.datetime1,
    t1.num1,
    t2.num2;

Demo

Answered By – Tim Biegeleisen

Answer Checked By – Mary Flores (BugsFixing Volunteer)