How can i convert a comma separated columns into junction table?

Question

I am pretty new to mysql and this site. I got an old mysql database (100.000 entries) to migrate to our new system. This is the old table:

CUSTOMER

Customer_ID    Name    Categories
1              Bob     1,2
2              Phil    NULL
3              Ines    10,8
4              Carol   1
5              Rick    13,2

And i need the following structure:

CUSTOMER

Customer_ID    Name
1              Bob 
2              Phil   
3              Ines   
4              Carol 
5              Rick

Category

Category_ID    Category_Name
1              Biker
2              Doctors
3              Teacher
...             ...
13              Drivers

CustomerHasCategory

Customer_ID    Category_ID  
1              1 
1              2  
3              10
3              8   
4              1
5              13 
5              2

Thanks for any help.

The Customer Table holds more data than categories.. i just edited an example. Customer Table has data like: surname, name, street etc. — Squarius, Feb 07 '16 at 14:14

score 0 · Answer 1 · answered Aug 24 '22 at 14:59

I also had this problem but not in MySQL. I solved it with Python using the Pandas library. So, the exact steps I followed won't be useful for you. However, I'll show you the general idea behind the solution I used.

Below is image of the original column