CODEWITHSUNDEEP
mysqlsql
yanyu
yanyu

Reputation: 227

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

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

Upvotes: 0

Views: 262

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

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;

Upvotes: 1

Related Questions