I want to a MySQL query to select the records separately from following table
ID AgentID Name Return Date
1 1,2,3 A 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 A 2016-05-22
1 2 A 2016-02-1
1 3 A 2016-1-15
2 2 B 2016-03-22
2 4 B 2016-04-1
You can use MySQL SUBSTRING_INDEX(). It will return the sub-string from the given comma separated string before a specified number of occurrences of the delimiter.
Try this, It seems to 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
FROM table1 t CROSS JOIN
(
SELECT a.N + b.N * 10 + 1 n
FROM
(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
ORDER BY n
) n
WHERE n.n <= 1 + (LENGTH(t.Return_Date) - LENGTH(REPLACE(t.Return_Date, ',', '')))
ORDER BY ID;
Check this.. SQL Fiddle HERE
For further Study go on MySQL Split String Function
if your values in tblA and you want to insert into tblB than query like this
insert into tblB (date) select date from tblA;
INSERT INTO second_table (
Field_1,
Field_2,
Field_3)
SELECT Field_1,
Field_2,
Field_3
FROM first_table;