I don't think you need this to be recurring task and the tags don't make much sense to me.
Although, you could do something in SQL like this:
Look here for
Common Table Expressions[
^].
WITH Family_Father (FamilyId, SerialNumber, Name)
AS
(
SELECT FamilyId, SerialNumber, Name
FROM #Temp
WHERE RelationCode = 1
)
SELECT
#Temp.FamilyId,
#Temp.MemberId,
#Temp.SerialNumber,
#Temp.Name,
#Temp.Age,
#Temp.RelationCode,
CASE WHEN (RelationCode <> 1) THEN Family_Father.SerialNumber ELSE 0 END As SNoFather,
CASE WHEN (RelationCode <> 1) THEN Family_Father.Name ELSE '' END AS FatherName
FROM
#Temp INNER JOIN Family_Father ON
#Temp.FamilyId = Family_Father.FamilyId