T-sql recursive query

Question

Based on an existing table, I used a CTE recursive query to retrieve the following data. But the inability to apply it even further.

Data below

id name parentid -------------------------- 1 project 0 2 structure 1 3 path_1 2 4 path_2 2 5 path_3 2 6 path_4 3 7 path_5 4 8 path_6 5 

I want to recursively form complete paths from the above data. The recursion tool will give the following result.

 FullPaths ------------- Project Project\Structure Project\Structure\Path_1 Project\Structure\Path_2 Project\Structure\Path_3 Project\Structure\Path_1\path_4 Project\Structure\Path_2\path_5 Project\Structure\Path_3\path_6 

thanks

Answer 1

Here is a CTE example for this:

 declare @t table (id int, name varchar(max), parentid int) insert into @t select 1, 'project' , 0 union all select 2, 'structure' , 1 union all select 3, 'path_1' , 2 union all select 4, 'path_2' , 2 union all select 5, 'path_3' , 2 union all select 6, 'path_4' , 3 union all select 7, 'path_5' , 4 union all select 8, 'path_6' , 5 ; with CteAlias as ( select id, name, parentid from @tt where t.parentid = 0 union all select t.id, parent.name + '\' + t.name, t.parentid from @tt inner join CteAlias parent on t.parentid = parent.id ) select * from CteAlias 

Answer 2

Try something like this:

 WITH Recursive AS ( SELECT ID, CAST(PathName AS VARCHAR(500)) AS 'FullPaths', 1 AS 'Level' FROM dbo.YourTable WHERE ParentID = 0 UNION ALL SELECT tbl.ID, CAST(r.FullPaths + '\' + tbl.PathName AS VARCHAR(500)) AS 'FullPaths', r.Level + 1 AS 'Level' FROM dbo.YourTable tbl INNER JOIN Recursive r ON tbl.ParentID = r.ID ) SELECT * FROM Recursive ORDER BY Level, ID 

Output:

 ID FullPaths Level 1 project 1 2 project\structure 2 3 project\structure\path_1 3 4 project\structure\path_2 3 5 project\structure\path_3 3 6 project\structure\path_1\path_4 4 7 project\structure\path_2\path_5 4 8 project\structure\path_3\path_6 4 

Answer 3

Something like

 ;WITH MyCTE AS ( SELECT name AS FullPaths, id FROM MyTable WHERE parentid = 0 /*Normally it'd be IS NULL with an FK linking the 2 columns*/ UNION ALL SELECT C.FullPaths + '\' + M.name, M.id FROM MyCTE C JOIN MyTable M ON M.parentid = C.id ) SELECT FullPaths FROM MyCTE 

Answer 4

try the following:

 DECLARE @YourTable table (id int, nameof varchar(25), parentid int) INSERT @YourTable VALUES (1,'project',0) INSERT @YourTable VALUES (2,'structure',1) INSERT @YourTable VALUES (3,'path_1',2) INSERT @YourTable VALUES (4,'path_2',2) INSERT @YourTable VALUES (5,'path_3',2) INSERT @YourTable VALUES (6,'path_4',3) INSERT @YourTable VALUES (7,'path_5',4) INSERT @YourTable VALUES (8,'path_6',5) ;WITH Rec AS ( SELECT CONVERT(varchar(max),nameof) as nameof,id FROM @YourTable WHERE parentid=0 UNION ALL SELECT CONVERT(varchar(max),r.nameof+'\'+y.nameof), y.id FROM @yourTable y INNER jOIN Rec r ON y.parentid=r.id ) select * from rec 

exit:

 nameof ----------------------------------------------- project project\structure project\structure\path_1 project\structure\path_2 project\structure\path_3 project\structure\path_3\path_6 project\structure\path_2\path_5 project\structure\path_1\path_4 (8 row(s) affected) 

Answer 5

You will need to change the name of the table #test that I used.

 WITH cte(id, name, parentid) AS ( SELECT id, convert(varchar(128), name), parentid FROM #test WHERE parentid = 0 UNION ALL SELECT t.id, convert(varchar(128), c.name +'\'+t.name), t.parentid FROM #test t INNER JOIN cte c ON c.id = t.parentid ) SELECT name as FullPaths FROM cte order by id