有几种方法可以做到这一点：
您可以简单地连接表本身，在同一行上显示S和H电话号码，并使用NVL来区分它们。
NVL本质上是一个if/then/else函数，用于“如果第一个值为NULL，则使用第二个值”
伪查询，可能需要调整以处理您的数据：

SELECT A.FULL_NM, S.PH_NO AS S_PH_NO, H.PH_NO AS H_PH_NO, NVL(S.PH_NO, H.PH_NO) AS PH_NO
FROM
    EODS_STG.STG1_EODS_PRD4_AGENT A
    LEFT OUTER JOIN EODS_STG.STG1_EODS_PRD4_CONTACT S ON (A.EEP_AGENT_NUMBER = S.EEP_AGENT_CONTACT_NUMBER AND A.EEP_AGENT_COMPANY = S.EEP_AGENT_CONTACT_COMPANY AND ADDR_TYPE = 'S')
    LEFT OUTER JOIN EODS_STG.STG1_EODS_PRD4_CONTACT H ON (A.EEP_AGENT_NUMBER = H.EEP_AGENT_CONTACT_NUMBER AND A.EEP_AGENT_COMPANY = H.EEP_AGENT_CONTACT_COMPANY AND ADDR_TYPE = 'H')

正如在注解中提到的，使用CASE语句也是可行的，只要数据表可以连接，那么单个代理的所有数据都将在一行中返回。

使用ROW_NUMBER解析函数和CASE表达式为行给予优先级：

SELECT *
FROM   (
  SELECT TRIM(A.EEP_AGENT_FIRST_NAME)
         || ' '
         || TRIM(A.EEP_AGENT_MIDDLE_NAME)
         || ' '
         || TRIM(A.EEP_AGENT_LAST_NAME) AS FULL_NM,
         TRIM(C.EEP_AGENT_CONTACT_EMAIL_ADDR) AS EMAIL_ADDR,
         TRIM(C.EEP_AGENT_CONTACT_PHONE_NUMBER) AS PH_NO,
         TRIM(C.EEP_AGENT_CONTACT_ADDR_TYPE) AS ADDR_TYPE,
         TRIM(A.EEP_AGENT_NUMBER) AS EEP_AGENT_NUMBER,
         TRIM(A.EEP_AGENT_COMPANY) AS EEP_AGENT_COMPANY,
         ROW_NUMBER() OVER (
           PARTITION BY
             TRIM(A.EEP_AGENT_COMPANY),
             TRIM(A.EEP_AGENT_NUMBER)
           ORDER BY
             CASE
             WHEN TRIM(C.EEP_AGENT_CONTACT_PHONE_NUMBER) IS NOT NULL
             AND  TRIM(C.EEP_AGENT_CONTACT_ADDR_TYPE) = 'S'
             THEN 1
             WHEN TRIM(C.EEP_AGENT_CONTACT_PHONE_NUMBER) IS NOT NULL
             AND  TRIM(C.EEP_AGENT_CONTACT_ADDR_TYPE) = 'H'
             THEN 2
             ELSE 3
             END
         ) AS rn
  FROM   EODS_STG.STG1_EODS_PRD4_AGENT A
         INNER JOIN EODS_STG.STG1_EODS_PRD4_CONTACT C
         ON  A.EEP_AGENT_NUMBER = C.EEP_AGENT_CONTACT_NUMBER
         AND A.EEP_AGENT_COMPANY = C.EEP_AGENT_CONTACT_COMPANY
)
WHERE rn = 1
ORDER BY
       EEP_AGENT_NUMBER,
       EEP_AGENT_COMPANY

• 注：你真的需要TRIM一切吗？当然，最好是在INSERT数据时执行一次，而不是每次SELECT数据时都执行一次。*