sql - how to split one row to many based on a comma separate column -

here data in mysql table:

name    childid city    11,12,13 maintain    21,22,29 pool    33,39,100

i want data below format. can use join tables.

11    city 12    city 13    city 21    maintain 22    maintain 29    maintain 33    pool 39    pool 100   pool

this bad format, no doubt comments , other answers tell you. want junction table -- , perhaps why want query.

the simplest way if have table of valid child ids. can do:

select c.id, d.name mydata d join      children c      on find_in_set(c.id, childid) > 0;

Sort