Splitting multiple cells in mySQL into multiple rows

Question

I have the following table.

Table1

ID   icc      bcci
1    402,403  101,102
2    403,404  103,105,106

I want to fetch the result as following

Result

ID    icc    bcci
1     402    101
1     402    102
1     403    101
1     403    102
2     403    103
2     403    105
2     403    106
2     404    103
2     404    105
2     404    106

Now you see why placing comma delimited values in a single column is such a bad idea — RiggsFolly, Sep 30 '19 at 10:29
Is there a reason why you can't normalize the database? If you need to query for the comma separated values mysql provides group_concat, but splitting them apart is more difficult. — Rob Lambden, Sep 30 '19 at 10:31

Strawberry · Accepted Answer · 2019-09-30T10:59:37.227

I have a table of integers - ints(i) running from 0-9...

DROP TABLE IF EXISTS my_bad_table;

CREATE TABLE my_bad_table
(id SERIAL PRIMARY KEY
,icc VARCHAR(255) NOT NULL
,bcci VARCHAR(255) NOT NULL
);

INSERT INTO my_bad_table VALUES
(1,'402,403','101,102'),
(2,'403,404','103,105,106');

[CREATE TABLE my_good_table AS]
SELECT DISTINCT a.id
     , a.icc
     , b.bcci
  FROM
(
SELECT DISTINCT id
              , SUBSTRING_INDEX(SUBSTRING_INDEX(icc,',',i+1),',',-1)icc
              , SUBSTRING_INDEX(SUBSTRING_INDEX(bcci,',',i+1),',',-1)bcci
           FROM my_bad_table
              , ints
) a
JOIN
(
SELECT DISTINCT id
              , SUBSTRING_INDEX(SUBSTRING_INDEX(icc,',',i+1),',',-1)icc
              , SUBSTRING_INDEX(SUBSTRING_INDEX(bcci,',',i+1),',',-1)bcci
           FROM my_bad_table
              , ints
) b
ON b.id = a.id
ORDER 
BY id,icc,bcci;

+----+-----+------+
| id | icc | bcci |
+----+-----+------+
|  1 | 402 | 101  |
|  1 | 402 | 102  |
|  1 | 403 | 101  |
|  1 | 403 | 102  |
|  2 | 403 | 103  |
|  2 | 403 | 105  |
|  2 | 403 | 106  |
|  2 | 404 | 103  |
|  2 | 404 | 105  |
|  2 | 404 | 106  |
+----+-----+------+

And then add a PRIMARY KEY on id, icc, bcci.

Actually, for your situation a simpler query will suffice, but hopefully you get the idea.

Splitting multiple cells in mySQL into multiple rows

1 Answers1