将查询 Package 在子查询中，并使用UNPIVOT和多个值：

SELECT policy_number,
       agent_number,
       agnt_pct_rt,
       num
FROM   (
  SELECT TRIM(UPPER(M.SC_CNT_PREF))
         || TRIM(UPPER(M.SC_CNT_NO))
         || TRIM(UPPER(M.SC_CNT_SUF)) AS POLICY_NUMBER,
         CASE WHEN C.SP_AGTNMBR1 != '00000' THEN 'CL000'|| C.SP_AGTNMBR1 END
           AS AGENT_NUMBER_1,
         TO_NUMBER(C.SP_AGTPCNT1) AS AGNT_PCT_RT_1,
         CASE WHEN C.SP_AGTNMBR2 != '00000' THEN 'CL000'|| C.SP_AGTNMBR2 END
           AS AGENT_NUMBER_2,
         TO_NUMBER(C.SP_AGTPCNT2) AS AGNT_PCT_RT_2,
         CASE WHEN C.SP_AGTNMBR3 != '00000' THEN 'CL000'|| C.SP_AGTNMBR3 END
           AS AGENT_NUMBER_3,
         TO_NUMBER(C.SP_AGTPCNT3) AS AGNT_PCT_RT_3
  FROM   /*EODS_STG.*/STG1_EODS_SCIS_MASTER M
         LEFT OUTER JOIN /*EODS_STG.*/STG1_EODS_SCIS_SPIA_CONTRACT C
         ON (
               M.SC_CNT_PREF = C.SP_CNTRPREF
           AND M.SC_CNT_NO = C.SP_CNTRNMBR
           AND M.SC_CNT_SUF = C.SP_CNTRSUFF
         )
)
UNPIVOT (
  (agent_number, agnt_pct_rt) FOR num IN (
    (agent_number_1, agnt_pct_rt_1) AS 1,
    (agent_number_2, agnt_pct_rt_2) AS 2,
    (agent_number_3, agnt_pct_rt_3) AS 3
  )
)

fiddle

根据您发布的数据和解释，union可能是这个问题的简单解决方案。
样本数据：

SQL> WITH
  2     test (policy_number,
  3           agent_number_1,
  4           agnt_pct_rt_1,
  5           agent_number_2,
  6           agnt_pct_rt_2)
  7     AS
  8        (SELECT 'SC123456789', 'CL00022250', 50, 'CL00050083', 25 FROM DUAL)

查询方式：

9  SELECT policy_number, agent_number_1 AS agent_number, agnt_pct_rt_1 AS agnt_pct_rt
 10    FROM test
 11  UNION ALL
 12  SELECT policy_number, agent_number_2, agnt_pct_rt_2 FROM test;

POLICY_NUMB AGENT_NUMB AGNT_PCT_RT
----------- ---------- -----------
SC123456789 CL00022250          50
SC123456789 CL00050083          25

SQL>