MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (id INT, value VARCHAR(255))
SELECT 1 id, '1,2,3'     value UNION ALL
SELECT 2   , '4,5,6,7,8'       ;
SELECT * FROM test;

Records: 2  Duplicates: 0  Warnings: 0




id
value


1
1,2,3

2
4,5,6,7,8


WITH 
cte1 AS ( SELECT 1 num UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5),
cte2 AS ( SELECT id FROM test ),
cte3 AS ( SELECT test.id, jsontable.*
          FROM test
          CROSS JOIN JSON_TABLE( CONCAT('["', REPLACE(test.value, ',', '","'), '"]'),
                                 "$[*]" COLUMNS( num FOR ORDINALITY,
                                                 value VARCHAR(254) PATH "$" )
                               ) AS jsontable )
SELECT cte2.id ColumnID, 
       cte1.num ColumnLevel, 
       COALESCE(cte3.value, 0) value
FROM cte1
CROSS JOIN cte2
LEFT JOIN cte3 ON cte1.num = cte3.num
              AND cte2.id = cte3.id
ORDER BY cte2.id, cte1.num


ColumnID
ColumnLevel
value


1
1
1

1
2
2

1
3
3

1
4
0

1
5
0

2
1
4

2
2
5

2
3
6

2
4
7

2
5
8


WITH 
cte1 AS ( SELECT 1 num UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5),
cte2 AS ( SELECT id, num 
          FROM test
          CROSS JOIN cte1 ),
cte3 AS ( SELECT test.id, jsontable.*
          FROM test
          CROSS JOIN JSON_TABLE( CONCAT('["', REPLACE(test.value, ',', '","'), '"]'),
                                 "$[*]" COLUMNS( num FOR ORDINALITY,
                                                 value VARCHAR(254) PATH "$" )
                               ) AS jsontable )
SELECT cte2.id ColumnID, 
       cte2.num ColumnLevel, 
       COALESCE(cte3.value, 0) value
FROM cte2
LEFT JOIN cte3 USING (id, num)
ORDER BY cte2.id, cte2.num


ColumnID
ColumnLevel
value


1
1
1

1
2
2

1
3
3

1
4
0

1
5
0

2
1
4

2
2
5

2
3
6

2
4
7

2
5
8