Oracle 18c

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

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (Common_ID, Type, Person_ID) AS
SELECT 123, 0, 78 FROM DUAL UNION ALL
SELECT 123, 2, 89 FROM DUAL UNION ALL
SELECT 123, 2, 63 FROM DUAL UNION ALL
SELECT 123, 2, 26 FROM DUAL UNION ALL
SELECT 456, 0, 99 FROM DUAL UNION ALL
SELECT 456, 2, 13 FROM DUAL;
6 rows affected

SELECT *
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (PARTITION BY common_id, type ORDER BY person_id) AS rn
  FROM   table_name t
)
PIVOT (
  MAX(person_id)
  FOR (type, rn) IN (
    (0, 1) AS primary,
    (2, 1) AS other1,
    (2, 2) AS other2,
    (2, 3) AS other3
  )
)


COMMON_ID
PRIMARY
OTHER1
OTHER2
OTHER3


123
78
26
63
89

456
99
13
null
null