MySQL split cell into multiple rows but copying the remaining row data

Question

We have a table like the following:

|  SKU    | Manufacturer | Qty
|-----------------------------|
|  12345  | GE, Sony     | 10
|  123456 | GE           | 25

We need to split the rows where more than one manufacturer is listed, and copy the remaining row data (SKU is not unique). So the expected results would be following:

|  SKU    | Manufacturer | Qty
|-----------------------------|
|  12345  | GE           | 10 |
|  12345  | Sony         | 10 |
|  123456 | GE           | 25 |

Can this be even done with MySQL, if so the how? If not, what would other options be then?

will you use that data somewhere like PHP? it's easier to do it there. — Taha Paksu, Nov 15 '17 at 08:03
Hope you've now learned to never, ever store data as comma separated items... — jarlh, Nov 15 '17 at 08:04
You should search before asking: https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows — Pham X. Bach, Nov 15 '17 at 08:04

score 1 · Accepted Answer · answered Nov 15 '17 at 08:09

Try the answer:

CREATE TABLE Temp (SKU INT, Manufacturer VARCHAR(15),Qty INT);
INSERT INTO Temp VALUES(12345,'GE, Sony',10);
INSERT INTO Temp VALUES(123456,'GE',25);

select
  Temp.SKU,
  SUBSTRING_INDEX(SUBSTRING_INDEX(Temp.Manufacturer, ',', numbers.n), ',', -1) Manufacturer,
  Temp.Qty
from
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5) numbers INNER JOIN Temp
  on CHAR_LENGTH(Temp.Manufacturer)
     -CHAR_LENGTH(REPLACE(Temp.Manufacturer, ',', ''))>=numbers.n-1
order by
  SKU, n

Check with SQL Fiddle

MySQL split cell into multiple rows but copying the remaining row data

1 Answers1