MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t1
SELECT * 
FROM ( SELECT 1, 'A,B,C,D', '1,2,3,4' UNION ALL
       SELECT 2, 'A,B,C', '1,3,8' 
      ) AS data (id, col1, col2);
SELECT * FROM t1;

Records: 2  Duplicates: 0  Warnings: 0




id
col1
col2


1
A,B,C,D
1,2,3,4

2
A,B,C
1,3,8


CREATE TABLE t2 (id INT, A INT, B INT, C INT, D INT);
INSERT INTO t2 (id, A, B, C, D)
WITH RECURSIVE 
cte AS (
    SELECT SUBSTRING_INDEX(col1, ',', 1) column_name,
           SUBSTRING_INDEX(col2, ',', 1) column_value,
           SUBSTRING(col1 FROM LOCATE(',', col1) + 1) names_tail,
           SUBSTRING(col2 FROM LOCATE(',', col2) + 1) values_tail,
           id
    FROM t1
UNION ALL
    SELECT SUBSTRING_INDEX(names_tail, ',', 1) column_name,
           SUBSTRING_INDEX(values_tail, ',', 1) column_value,
           SUBSTRING(names_tail FROM LOCATE(',', names_tail) + 1) names_tail,
           SUBSTRING(values_tail FROM LOCATE(',', values_tail) + 1) values_tail,
           id
    FROM cte
    WHERE column_name <> names_tail
)
SELECT id,
       MAX(CASE WHEN column_name = 'A' THEN column_value END) A,
       MAX(CASE WHEN column_name = 'B' THEN column_value END) B,
       MAX(CASE WHEN column_name = 'C' THEN column_value END) C,
       MAX(CASE WHEN column_name = 'D' THEN column_value END) D
FROM cte
GROUP BY id;

Records: 2  Duplicates: 0  Warnings: 0


SELECT * FROM t2;


id
A
B
C
D


1
1
2
3
4

2
1
3
8
null
id	A	B	C	D
1	1	2	3	4
2	1	3	8	null