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

Question

here is my data in mysql table:

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

I want to get data as the below format. So I can use it to join another tables.

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

I would run jdbc/pdo/whatever query "select * from table", use explode() function and loop/insert it in a new table. — oshell, Mar 17 '15 at 10:10
While you probably _can_ do it in MySQL, I'd suggest to do it in Python/Ruby/PHP or whatever your server script language is. — Paul, Mar 17 '15 at 10:15
don't ask similar type of questions these are already present in stackoverflow please surf folks.....here is link .. http://stackoverflow.com/questions/14811316/separate-comma-separated-values-and-store-in-table-in-sql-server — koushik veldanda, Mar 17 '15 at 10:17

score 1 · Accepted Answer · answered Mar 17 '15 at 10:38

This is a very bad format, as no doubt comments and other answers will tell you. You want a junction table -- and this is perhaps why you want this query.

The simplest way is if you have a table of all valid child ids. Then you can do:

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

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

1 Answers1