Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE table1 (family_ref, fromdate, Name, Weight, "NUMBER", Mother_number) AS
SELECT 1, DATE '2021-07-06', 'AA', 100, 1, 2 FROM DUAL UNION ALL
SELECT 1, DATE '2021-07-06', 'BB', 200, 2, 3 FROM DUAL UNION ALL
SELECT 1, DATE '2021-07-06', 'CC', 300, 3, 2 FROM DUAL UNION ALL
SELECT 1, DATE '2021-07-06', 'DD', 400, 4, 5 FROM DUAL UNION ALL
SELECT 1, DATE '2021-07-06', 'EE', 500, 5, 6 FROM DUAL;
5 rows affected

CREATE TABLE table2 (family_ref) AS
SELECT 1 FROM DUAL;
1 rows affected

SELECT to_char(a.fromdate, 'dd-mm-yyyy') as fromdate,
       a.Name,
       a.Weight,
       a."NUMBER",
       a.Mother_number,
       PRIOR weight AS mother_weight
FROM   table1 a
       INNER JOIN table2 b
       ON (a.family_ref=b.family_ref)
WHERE  LEVEL = 2
OR     (   LEVEL = 1
       AND NOT EXISTS(
             SELECT 1
             FROM   table1 x
             WHERE a.mother_number = x."NUMBER"
           )
       )
CONNECT BY NOCYCLE
       PRIOR "NUMBER" = mother_number
AND    PRIOR a.family_ref = a.family_ref
ORDER BY a."NUMBER"


FROMDATE
NAME
WEIGHT
NUMBER
MOTHER_NUMBER
MOTHER_WEIGHT


06-07-2021
AA
100
1
2
200

06-07-2021
BB
200
2
3
300

06-07-2021
CC
300
3
2
200

06-07-2021
DD
400
4
5
500

06-07-2021
EE
500
5
6
null


WITH data (fromdate, name, weight, "NUMBER", mother_number) AS (
  SELECT to_char(a.fromdate, 'dd-mm-yyyy'),
         a.Name,
         a.Weight,
         a."NUMBER",
         a.Mother_number
  FROM   table1 a
         INNER JOIN table2 b
         ON (a.family_ref=b.family_ref)
)
SELECT d.*,
       m.weight AS mother_weight
FROM   data d
       LEFT OUTER JOIN data m
       ON (d.mother_number = m."NUMBER")
ORDER BY d."NUMBER"


FROMDATE
NAME
WEIGHT
NUMBER
MOTHER_NUMBER
MOTHER_WEIGHT


06-07-2021
AA
100
1
2
200

06-07-2021
BB
200
2
3
300

06-07-2021
CC
300
3
2
200

06-07-2021
DD
400
4
5
500

06-07-2021
EE
500
5
6
null

FROMDATE	NAME	WEIGHT	NUMBER	MOTHER_NUMBER	MOTHER_WEIGHT
06-07-2021	AA	100	1	2	200
06-07-2021	BB	200	2	3	300
06-07-2021	CC	300	3	2	200
06-07-2021	DD	400	4	5	500
06-07-2021	EE	500	5	6	null