Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE people (id, name, gender) AS
SELECT 101, 'Riya',   'F' FROM DUAL UNION ALL
SELECT 566, 'Aman',   'M' FROM DUAL UNION ALL
SELECT 202, 'Rakesh', 'M' FROM DUAL UNION ALL
SELECT 875, 'lucky',  'M' FROM DUAL UNION ALL
SELECT 202, 'Reena',  'F' FROM DUAL UNION ALL
SELECT 322, 'Raina',  'F' FROM DUAL UNION ALL
SELECT 345, 'Rohit',  'M' FROM DUAL UNION ALL
SELECT 322, 'Mohit',  'M' FROM DUAL UNION ALL
SELECT 345, 'Meena',  'F' FROM DUAL;
9 rows affected

CREATE TABLE relations (cid, pid) AS
SELECT 101, 202 FROM DUAL UNION ALL
SELECT 566, 322 FROM DUAL UNION ALL
SELECT 875, 345 FROM DUAL;
3 rows affected

SELECT *
FROM   (
  SELECT c.name AS child,
         p.name AS parent,
         p.gender
  FROM   relations r
         INNER JOIN people c
         ON r.cid = c.id
         INNER JOIN people p
        ON r.pid = p.id
)
PIVOT (
  MAX(parent) FOR gender IN (
    'M' AS father,
    'F' AS mother
  )
)


CHILD
FATHER
MOTHER


Riya
Rakesh
Reena

Aman
Mohit
Raina

lucky
Rohit
Meena