How to split columns to multiple rows in Oracle SQL

Question

POLICY_NUMBER	AGENT_NUMBER_1	AGNT_PCT_RT_1	AGENT_NUMBER_2	AGNT_PCT_RT_2
SC123456789	CL00022250	50	CL00050083	25

We have sample data as shown in the above table, based on which we need to split the data into separate rows accordingly if two or three agents are attached to a policy, then each have their own record and percentage as shown below.

SC123456789 ---> CL00022250 ----> 50
SC123456789 ---> CL00050083 ----> 25

SQL Query Used :

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
    )

Answer 1

You wrap your query inside a sub-query and use UNPIVOT with multiple values:

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