SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE your_table ( id INT AUTO_INCREMENT PRIMARY KEY, parent_id INT, name VARCHAR(255) NOT NULL ); INSERT INTO your_table (parent_id, name) VALUES (NULL, 'Root'), (1, 'Child 1'), (1, 'Child 2'), (2, 'Grandchild 1'), (3, 'Grandchild 2'); CREATE TABLE tree ( id INT, parent_id INT, name VARCHAR(255), level INT ); WITH RECURSIVE t (id, parent_id, name, level) AS ( SELECT id, parent_id, name, 1 AS level FROM your_table WHERE parent_id IS NULL UNION ALL SELECT t1.id, t1.parent_id, t1.name, level + 1 FROM your_table t1 JOIN t ON t1.parent_id = t.id ) INSERT INTO tree (id, parent_id, name, level) SELECT id, parent_id, name, level FROM t ORDER BY level, name;