Mysql split one column on the basis of another column

Question

I have two columns in mysql one contains comma separated names of fields and other contains the values for those comma separated names

example

COL1            COL2

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

A has value 1 and so on ,

Now I want to insert these values in another table in their separate column

    A B C D
    1 2 3 4
    1 3 8

I tried splitting the values using a function

DELIMITER $$

CREATE FUNCTION SPLIT_ST(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255) DETERMINISTIC
BEGIN 
    RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, ',');
END$$

SELECT SPLIT_ST(COL2, ',', 1) as A,

But the problem is some columns might not have all the fields as shown in example

I just want to do that

Insert into table (A,B,C,D) select from the table2

Please help

Which version of MySQL? MySQL 5.x and MySQL 8+ are very different in their capabilities. Do you ***always*** have the same number of ***known*** columns? *(Is it always A,B,C,D in the destination table?)* SQL is statically typed, and haveing the number (and names) of columns varying depending on the data is an anti-pattern and ***extremely*** slow/messy. You may be better off with a normalised pattern of `id, letter, number` which will give you one row per pair *(instead of trying to have a table of varying width)*. — MatBailie, Mar 04 '22 at 11:01
there a better function for what you seek, still you need to process the endresult to fill up with NULL use one of these function https://stackoverflow.com/questions/14950466/how-to-split-the-name-string-in-mysql also you should think about normalization read up on this https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — nbk, Mar 04 '22 at 11:17
@MatBailie i am using mysql 5.x ,and its a one time job ,just need to dump the data into new table which has columns as per the comma separated names — gANDALF, Mar 04 '22 at 12:35
there are 20 columns ,but I need to insert only in the columns that are named inside table column separated by comma — gANDALF, Mar 04 '22 at 15:27

score 0 · Answer 1 · answered Mar 04 '22 at 11:27

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;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=c5f4bf5417ecfd9bb855abb6da0c6214

But I completely agree with

You may be better off with a normalised pattern of id, letter, number which will give you one row per pair (instead of trying to have a table of varying width). – MatBailie

OP clarified MySQL 5.x, and it ***appears*** that there is no `id` column anyway *(OP chose to not clarify that)*. — MatBailie, Mar 04 '22 at 13:44

Mysql split one column on the basis of another column

1 Answers1