You can do with While loop and Temp Table
--DECLARED A DUMMY TABLE WITH DATA
DECLARE @Contacts TABLE (ContactID INT, ContactName VARCHAR(20),ParentID INT)
INSERT INTO @Contacts
SELECT 10, 'xyz', 0
UNION ALL
SELECT 11, 'pqr', 10
UNION ALL
SELECT 12, 'abc', 11
UNION ALL
SELECT 13, 'lmn', 12
--ACTUAL QUERY STARTS HERE
DECLARE @ContactId INT
DECLARE @ContactName VARCHAR(20)
DECLARE @ParentId INT
DECLARE @tmp TABLE (ContactID INT, ContactName VARCHAR(20),ParentID INT)
SET @ContactId = 13
WHILE (@ContactId <> 0)
BEGIN
SELECT @ContactId = ContactId, @ParentId=ParentId, @ContactName=ContactName
FROM @Contacts WHERE ContactId = @ContactId
INSERT INTO @tmp
VALUES(@ContactId, @ContactName, @ParentId)
SET @ContactId = @ParentId
END
SELECT * FROM @tmp
