SQL Join table to itself

I have a table where there are two columns like below.

value1  DerivedFrom
1         0
2         1
3         2
4         3
5         4

Basically, what it is saying is 1 was new, 2 was derived from 1, 3 was derived from 2 and so on.

I want the out put with 1 as the master key and 2,3,4 and 5 as children.

value1  DerivedFrom
1        0
1        1
1        2
1        3
1        4

Is it achiveble in SQL ? Thanks in advance

Answer

As mentioned in the comment, the simplest way is with an rCTE (recursive Common Table Expression):

--Sample Data
WITH YourTable AS(
    SELECT *
    FROM (VALUES(1,0),
                (2,1),
                (3,2),
                (4,3),
                (5,4))V(value1,DerivedFrom)),
--Solution
rCTE AS(
    SELECT YT.value1 as rootValue,
           YT.value1,
           YT.DerivedFrom
    FROM YourTable YT
    WHERE YT.DerivedFrom = 0
    UNION ALL
    SELECT r.rootValue,
           YT.value1,
           YT.DerivedFrom
    FROM YourTable YT
         JOIN rCTE r ON YT.DerivedFrom = r.value1)
SELECT r.rootValue AS value1,
       r.DerivedFrom
FROM rCTE r;