5

I have the following structure:

Example Tree:

Tree Diagram

Example Table:

    [table]

    id  parent
    ----------
    1   NULL
    2   1
    3   1
    4   2
    5   2
    6   2
    7   3
    8   4

For each id, I want to list all of its parents, including its ascendants in a separate row.

Desired Output:

    id  parent
    ----------
    1   NULL
    2   1
    3   1
    4   1
    4   2
    5   1
    5   2
    6   1
    6   2
    7   1
    7   3
    8   1
    8   2
    8   4

I tried to use a CTE, but I can't seem to wrap my head around it.

    with temp(id,parent) as (
    SELECT S.id, S.parent
    FROM [table] as S 

    UNION ALL

    SELECT S2.id, S2.parent
    FROM [table] as S2 
    inner join temp on S2.id=temp.parent and temp.id is not null
    )

    SELECT * FROM temp order by id

I'm trying to traverse up a hierarchy tree and list all the nodes it hits in a separate line for all starting points of the tree. This is how the sample data was given to me.

8

You were on the right path and almost had it. In the select of the second section the parent column should come from the cte instead of S2 and also in the second section the join was backwards (S2.id = p.parent vs S2.parent = p.id). But that's it!

create table [table]
(
id int,
parent int
);

insert into [table] values(1,NULL),
    (2,1),
    (3,1),
    (4,2),
    (5,2),
    (6,2),
    (7,3),
    (8,4);


;WITH ctetable(id, parent, depth, path) as 
(
    SELECT S.id, S.parent, 1 AS depth, convert(varchar(100), S.id) AS path
    FROM [table] as S
    UNION ALL
    SELECT S2.id, p.parent, p.depth + 1 AS depth, convert(varchar(100), (RTRIM(p.path) +'->'+ convert(varchar(100), S2.id)))
    FROM ctetable AS p JOIN [table] as S2 on S2.parent = p.id
    WHERE p.parent is not null
)
SELECT * FROM ctetable ORDER BY id, parent;
  • I added a quick explain. They were super close. – indiri Sep 14 '17 at 20:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.