DROP TEMPORARY TABLE IF EXISTS tempFinalData;
CREATE TEMPORARY TABLE tempFinalData
(
Sn int,
ParentID int,
DataValue varchar(10),
Depth tinyint,
Navi varchar(50)
);
DROP TABLE IF EXISTS tempData;
CREATE TABLE tempData
(
Sn int,
ParentID int,
DataValue varchar(10)
);
INSERT INTO tempData (Sn, ParentID, DataValue) values (1, 0, 'A_0');
INSERT INTO tempData (Sn, ParentID, DataValue) values (2, 0, 'B_0');
INSERT INTO tempData (Sn, ParentID, DataValue) values (3, 1, 'A_1');
INSERT INTO tempData (Sn, ParentID, DataValue) values (4, 2, 'B_1');
INSERT INTO tempData (Sn, ParentID, DataValue) values (5, 1, 'A_2');
INSERT INTO tempData (Sn, ParentID, DataValue) values (6, 2, 'B_2');
INSERT INTO tempData (Sn, ParentID, DataValue) values (7, 3, 'A_3');
INSERT INTO tempData (Sn, ParentID, DataValue) values (8, 3, 'A_4');
INSERT INTO tempData (Sn, ParentID, DataValue) values (9, 7, 'A_5');
INSERT INTO tempData (Sn, ParentID, DataValue) values (10, 7, 'A_6');
INSERT INTO tempFinalData (Sn, ParentID, DataValue, Depth, Navi)
(
WITH RECURSIVE tempCteData (Sn, ParentID, DataValue, Depth, Navi)
AS
(
SELECT Sn, ParentID, DataValue, 0 AS Depth, cast(DataValue as char(500) charset utf8mb4) AS Navi
FROM tempData
WHERE ParentID = 0
UNION ALL
SELECT A.Sn, A.ParentID, A.DataValue, B.Depth+1 AS Depth, concat(B.DataValue, ' 〉 ', A.DataValue) AS Navi
FROM tempData AS A JOIN tempCteData AS B
ON A.ParentID = B.Sn AND A.ParentID > 0
)
SELECT Sn, ParentID, DataValue, Depth, Navi from tempCteData
);
SELECT * FROM tempFinalData;