您可以将子句添加到连接中，这样就不会从完整查询中排除空值

select T1.person_ID,T2.person_ID, T3.person_ID, T4.person_ID,  
from table_1 T1
left outer join table_1 T2 on T1.Common_ID=T2.Common_ID 
and T1.person_ID <> T2.person_ID
left outer join table_1 T3 on T1.Common_ID=T3.Common_ID
and T1.person_ID <> T3.person_ID
and T2.person_ID <> T3.person_ID
left outer join table_1 T4 on T1.Common_ID=T4.Common_ID
and T1.person_ID <> T4.person_ID
and T2.person_ID <> T4.person_ID
and T3.person_ID <> T4.person_ID
where T1.type=0
and T2.type=2
and T3.type=2
and T4.type=2

你可以先使用ROW_NUMBER解析函数，然后再使用PIVOT（并且可以在没有任何自连接的情况下完成）：

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
  )
)

其中，对于样本数据：

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;

输出：
| 通用ID|初级|其他1|其他2|其他3|
| --------------|--------------|--------------|--------------|--------------|
| 一百二十三|七十八|二十六|六十三|八九|
| 四五六|九九|十三|联系我们|联系我们|
fiddle

其中一个选项是连接来自同一个表的两个查询：

WITH        --  Sample data 
    tbl (COMMON_ID, A_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 
        )

主SQL

SELECT  t.PRIMARY, 
        Max(CASE RN WHEN 1 THEN PERSON_ID END) "OTHER_1",
        Max(CASE RN WHEN 2 THEN PERSON_ID END) "OTHER_2",
        Max(CASE RN WHEN 3 THEN PERSON_ID END) "OTHER_3"
FROM    ( Select  COMMON_ID, 
                  Max(CASE WHEN A_TYPE = 0 THEN PERSON_ID END) "PRIMARY"
          From    tbl
          Group By COMMON_ID
          Order By COMMON_ID  ) t
INNER JOIN  ( Select  ROW_NUMBER() OVER(Partition By COMMON_ID Order By PERSON_ID DESC) "RN", 
                      COMMON_ID, PERSON_ID
              From    tbl
              Where   A_TYPE = 2
            ) t2 ON(t2.COMMON_ID = t.COMMON_ID)
GROUP BY t.PRIMARY

回复：
| 初级|其他_1|其他_2|其他3|
| --------------|--------------|--------------|--------------|
| 七十八|八九|六十三|二十六|
| 九九|十三|空|空|
注意：如果有超过3个其他人的id，你也应该处理，要么把它们作为一个列表放在一列中，要么像这个答案一样。我命令其他人把最新的人的ID放在第一位，就像你预期的结果一样。