MySQL: from one record to many records, is it possible?

Question

I have a table like this:

code allValues
--------------
C001 1,2,3,4
C002 3,4,5
C003 6,7

and I need to obtain a table like this:

code Value
----------
C001 1
C001 2
C001 3
C001 4
C002 3
C002 4
C002 5
C003 6
C003 7

Ca I do it with a SQL statement? I'm using MySQL in PHPMyAdmin, unaware of which specific database engine.

First Split comma separated values and store in table and then join that table with main table — Sandip - Frontend Developer, Aug 26 '16 at 05:52
You can use php to split up the values or write a stored proc in mysql to do the same. So, the answer is yes, it is possible. — Shadow, Aug 26 '16 at 05:53
@Sandip Patel, I have to use only sql statements, no php. I removed php from the tag list. — tic, Aug 26 '16 at 05:57
We do not do requests. Show what you have and we will help. Alternatively, you could search SO. This question has been answered quite a few times already. — Shadow, Aug 26 '16 at 05:57

score 1 · Answer 1 · answered Aug 26 '16 at 06:01

If you have a look up table for each "allvalue", then you can do:

select t.code, lu.value
from t join
     lookup lu
     on find_in_set(lu.value, t.allvalues) > 0;

Another method -- if you know the maximum length of allvalues -- is to do string manipulation:

select t.code,
       substring_index(substring_index(t.allvalues, ',', n.n), ',', -1) as vaue
from t cross join
     (select 1 as n union all select 2 union all select 3 union all select 4
     ) n
     on n.n <= length(t.allvalues) - length(replace(t.allvalues, ',', ''));

This version assumes the maximum length is "4", as in the question.

MySQL: from one record to many records, is it possible?

1 Answers1