select - How to split comma separated string of records and arrange then sequentially in MySQL? -

i want mysql query select records separately following table

   id  agentid    name   return date    1   1,2,3           2016-05-22,2016-02-1,2016-1-15    2   2,4         b     2016-03-22,2016-04-1 

expecting answer

id  agentid    name   return date 1    1              2016-05-22 1    2              2016-02-1 1    3              2016-1-15 2    2          b     2016-03-22 2    4          b     2016-04-1 

you can use mysql substring_index(). return sub-string given comma separated string before specified number of occurrences of delimiter.

try this, seems work fine:

select id        ,substring_index(substring_index(t.agentid, ',', n.n), ',', -1) agent        ,name        ,substring_index(substring_index(t.return_date, ',', n.n), ',', -1) return_date table1 t cross join   (    select a.n + b.n * 10 + 1 n          (select 0 n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)     ,(select 0 n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) b    order n   ) n  n.n <= 1 + (length(t.return_date) - length(replace(t.return_date, ',', ''))) order id; 

check this.. sql fiddle here

for further study go on mysql split string function