sql - how to split a column into multiple columns in mysql -

i have column

 +--------------------------+ | marks                    | +--------------------------+ |maths-80,phy-100,che-99   | |maths-90,phy-60           |  |phy-82,che-65             | |che-90                    |
 |maths-33,phy-89,che-65    | |maths-50,phy-43,che-59    | +--------------------------+ 

give 3 sample subjects in realdata maybe number of subjects seperated comma. want display marks each subject split marks column , display in multiple columns below sample.

 +-------------------------------------+-------------+------------+ | marks                  | maths       | phy         | che        | +-------------------------------------+-------------+-------------+                                maths-80,phy-100,che-99  | 80          |   100      |     99      | maths-90,phy-60          | 90          |   60       |     0       | phy-82,che-65            |  0          |   82       |     65      | che-90                   | 90          |   0        |     0       | maths-33,phy-89,che-65   | 33          |   89       |     65      | maths-50,phy-43,che-59   | 50          |   43       |     59      | +-------------------------------------+-------------+-------------+  

with given example data can below -

mysql> create table test (marks varchar(100)); query ok, 0 rows affected (0.12 sec)  mysql> insert test values ('maths-80,phy-100,che-99'),('maths-90,phy-60'),('phy-82,che-65'),('che-90'),('maths-33,phy-89,che-65'),('maths-50,phy-43,che-59'); query ok, 6 rows affected (0.04 sec) records: 6  duplicates: 0  warnings: 0  mysql> select * test ; +-------------------------+ | marks                   | +-------------------------+ | maths-80,phy-100,che-99 | | maths-90,phy-60         | | phy-82,che-65           | | che-90                  | | maths-33,phy-89,che-65  | | maths-50,phy-43,che-59  | +-------------------------+ 6 rows in set (0.00 sec) 

now using locate , substring_index can extract values as

select marks,  case   when locate('maths',marks) > 0 substring_index(substring_index(marks,'maths-',-1),',',1) else 0 end  maths , case   when locate('phy',marks) > 0 substring_index(substring_index(marks,'phy-',-1),',',1) else 0 end  phy , case   when locate('che',marks) > 0 substring_index(substring_index(marks,'che-',-1),',',1) else 0 end  che  test ; 

output :

+-------------------------+-------+------+------+ | marks                   | maths | phy  | che  | +-------------------------+-------+------+------+ | maths-80,phy-100,che-99 | 80    | 100  | 99   | | maths-90,phy-60         | 90    | 60   | 0    | | phy-82,che-65           | 0     | 82   | 65   | | che-90                  | 0     | 0    | 90   | | maths-33,phy-89,che-65  | 33    | 89   | 65   | | maths-50,phy-43,che-59  | 50    | 43   | 59   | +-------------------------+-------+------+------+ 6 rows in set (0.00 sec)