;with data as (
select p.NUMBER_, p.PARENTPROJNUMBER, p.NAME, p.Value1
from yourtable p
where p.PARENTPROJNUMBER IS NULL --- presuming this are the "root parents", or the one you are looking for ...
UNION ALL -- here is the recursive stuff
select p.NUMBER_, p.PARENTPROJNUMBER, p.NAME, p.Value1
from DATA d
JOIN yourtable p
ON p.PARENTPROJNUMBER = d.NUMBER_
AND d.PARENTPROJNUMBER <> d.NUMBER_
)
select * from data
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
From novice to tech pro — start learning today.
to add the level, change like this
Open in new window