--=============================================================================
--      Create the test table.
--      Nothing in this section has to do with the solution of the problem.
--      We're just creating test data here.  You'll get faster/better answers
--      in the future if you provide the data for future posts in a similar
--      manner.  It would also be a great help if you posted actual working,
--      properly aliased code the produced your output instead of the snippet
--      that you posted ;-)
--=============================================================================
--===== Since we're dropping a test table, do this in a nice, safe place that
     -- everyone has so we don't accidently drop a real table.
    USE tempdb
;
--===== Suppress the auto-display of rowcounts to make the output prettier
     -- and to prevent returning false errors (as rowcounts) to the GUI
     -- if one is involved later on.
    SET NOCOUNT ON
;
--===== Conditionally drop the test table to make reruns in SSMS easier.
     -- (This is not a part of the solution.  We're just creating a test table here)
     IF OBJECT_ID('tempdb.dbo.RelationShip','U') IS NOT NULL
        DROP TABLE tempdb.dbo.RelationShip
;
--===== Create the test table with some indexing.
     -- (This is not a part of the solution.  We're just creating a test table here)
 CREATE TABLE tempdb.dbo.RelationShip
        (
        Child  INT PRIMARY KEY CLUSTERED,
        Parent INT
        )
;
--===== Populate the test table.
     -- (This is not a part of the solution.  We're just creating a test table here)
 INSERT INTO tempdb.dbo.RelationShip
        (Child, Parent)
 SELECT 1,0 UNION ALL
 SELECT 2,1 UNION ALL
 SELECT 3,2 UNION ALL
 SELECT 4,3 UNION ALL
 SELECT 5,4
;
GO
--=============================================================================
--      I assume that you're going to want to make the code reusable.  With
--      that in mind, I suggest the use of an INLINE TABLE VALUED FUNCTION
--      Like the following.  Note that we're still in TempDB but I kept the
--      3 part naming convention to prevent any accidents.  You should drop the
--      3 part naming for your production code.
--      Do notice the proper aliasing which prevents ambiguity errors.
--=============================================================================
--===== Conditionally drop the function to make reruns in SSMS easier.
     -- This drop wouldn't normally go into production
     IF OBJECT_ID('tempdb.dbo.GetDownlinePlusTopParent','IF') IS NOT NULL
        DROP FUNCTION dbo.GetDownlinePlusTopParent
;
GO
 CREATE FUNCTION dbo.GetDownlinePlusTopParent
        (@Child INT)
RETURNS TABLE -- WITH SCHEMABINDING --(Uncomment WITH SCHEMABINDING for production)
     AS 
 RETURN
   WITH CTE(Child, Parent, TopParent, Level) AS
        (
         SELECT Child, Parent, TopParent = Parent, Level = 0
           FROM dbo.Relationship
          WHERE Child = @Child
          UNION ALL
         SELECT r.Child, r.Parent, c.TopParent , Level = c.Level + 1
           FROM dbo.Relationship r
          INNER JOIN CTE c
             ON r.Parent = c.Child
        )
         SELECT Child, Parent, TopParent, Level
           FROM CTE
;
GO
--=============================================================================
--      Example usage:
--=============================================================================
--===== Suppress the auto-display of rowcounts to make the output prettier
     -- and to prevent returning false errors (as rowcounts) to the GUI
     -- if one is involved later on.
    SET NOCOUNT ON
;
--===== Do two different calls to the function to show how it's used.
 SELECT Child, Parent, TopParent, Level
   FROM dbo.GetDownlinePlusTopParent(1)
;
 SELECT Child, Parent, TopParent, Level
   FROM dbo.GetDownlinePlusTopParent(3)
;

Child       Parent      TopParent   Level
----------- ----------- ----------- -----------
1           0           0           0
2           1           0           1
3           2           0           2
4           3           0           3
5           4           0           4

Child       Parent      TopParent   Level
----------- ----------- ----------- -----------
3           2           2           0
4           3           2           1
5           4           2           2