t-sql recursive query

Question

Based on an existing table I used CTE recursive query to come up with following data. But failing to apply it a level further.

Data is as 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 full paths from the above data. Means the recursion will give the following output.

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's an example CTE to do that:

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 @t t
    where t.parentid = 0
    union all
    select t.id, parent.name + '\' + t.name, t.parentid
    from @t t
    inner join CteAlias parent on t.parentid = parent.id
)
select * 
from CteAlias

Answer 2

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 3

try this:

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

output:

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 4

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 5

You'll have to change the name of #test table I was using.

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