how to split single row to multiple rows in mysql

Question

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

Answer 1

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

Answer 2

Since version 8+ MySql supports LATERAL

select t.datetime1, t.count, n1.num1, n2.num2
from tbl t
cross join lateral (
   select SUBSTRING_INDEX(t.num1, ',', 1) AS num1
    UNION ALL
   select SUBSTRING_INDEX(t.num1, ',', -1)
   ) n1
cross join lateral (
   select SUBSTRING_INDEX(t.num2, ',', 1) AS num2
    UNION ALL
   select SUBSTRING_INDEX(t.num2, ',', -1)
   ) n2

db<>fiddle