SQLize | PHPize | SQLtest

CREATE TABLE [dbo].[area]( [id] [int] IDENTITY(1,1) NOT NULL, [name] [varchar](50) NOT NULL, [parent_id] [int] NULL, [color] [varchar](50) NULL, [priority] [int] NULL) insert into area values('root', NULL, NULL, NULL); insert into area values('test1', 1, NULL, NULL); insert into area values('test2', 2, 'red', 50); insert into area values('test3', 5, 'blue', 1); insert into area values('test4', 1, 'yellow', 10); WITH tPATH AS (SELECT id, parent_id, name, color, priority, 0 as [level], CAST(id AS NVARCHAR(MAX)) As path_id, CAST(name AS NVARCHAR(MAX)) As path_name FROM area WHERE parent_id is NULL UNION ALL SELECT area.id, area.parent_id, area.name, area.color, area.priority, [level] + 1, CONCAT(tPATH.path_id, '\', CAST(area.id AS NVARCHAR(MAX))), CONCAT(tPATH.path_name, '\', CAST(area.name AS NVARCHAR(MAX))) FROM area INNER JOIN tPATH ON area.parent_id = tPATH.id ) select * from tPATH;