Insert comma Separate values ​​through MySQL query

Question

I have one table (in phpmyadmin) with the following fields and structure:

Table 1 id | sponsor 1 | -1 2 | 1 3 | 1 4 | 2 5 | 4 6 | 4 

Now I want to insert the data from the table above into a new table, as shown below:

 Table 2 id | children 1 | 2,3 2 | 4 3 | 4 | 5,6 5 | 6 | 

This is actually a Tree structure that I saved in the mysql database.
I already wrote a script in php, but since table 1 has more than 100K lines, so it takes too much time. Please tell me an efficient SQL query to quickly complete this task.

Answer 1

Query:

SQLFIDDLE Example

 SELECT t1.id, (SELECT group_concat(id separator ', ') FROM table1 t2 WHERE t2.sponsor = t1.id) AS children FROM table1 t1 GROUP BY t1.id 

Result:

 | ID | CHILDREN | ----------------- | 1 | 2, 3 | | 2 | 4 | | 3 | (null) | | 4 | 5, 6 | | 5 | (null) | | 6 | (null) | 

Insert expression:

 INSERT INTO table2 SELECT t1.id, (SELECT group_concat(id separator ', ') FROM table1 t2 WHERE t2.sponsor = t1.id) AS children FROM table1 t1 GROUP BY t1.id 

Answer 2

This is similar to @Justin's answer , but uses a left join instead of a correlated subquery:

 INSERT INTO Table2 (id, children) SELECT sp.id, GROUP_CONCAT(ch.id) AS children FROM Table1 sp LEFT JOIN Table1 ch ON sp.id = ch.sponsor GROUP BY t1.id ; 

A demonstration of the result of the SELECT statement can be found (and reproduced) in the SQL Fiddle (the schema was borrowed from Justin).

Answer 3

One of your SELECT elements should be GROUP_CONCAT(...) as column , which will combine these values ​​separated by commas. If you want to filter out one of these values, you can use GROUP BY -whatever- HAVING find_in_set( -number- , column )

Answer 4

See if the following helps

 INSERT INTO table2 SELECT sponsor, GROUP_CONCAT(id) FROM table1 GROUP BY id