如何在Oracle SQL中将多代理关联列拆分为多行记录？

示例原始数据 #

需求说明 #

当一个保单关联2个或3个代理人时，需将对应数据拆分为单独行，使每个代理人拥有独立记录及对应比例，示例输出如下：

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

当前使用的SQL语句 #

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
    )

解决方案SQL #

通过UNION ALL将每个代理人的信息拆分为独立行，同时过滤掉无效的代理人编号（即等于'00000'的情况）：

SELECT
    POLICY_NUMBER,
    AGENT_NUMBER,
    AGNT_PCT_RT
FROM (
    -- 提取第一个代理人的数据
    SELECT
        TRIM(UPPER(M.SC_CNT_PREF)) || TRIM(UPPER(M.SC_CNT_NO)) || TRIM(UPPER(M.SC_CNT_SUF)) AS POLICY_NUMBER,
        'CL000' || C.SP_AGTNMBR1 AS AGENT_NUMBER,
        TO_NUMBER(C.SP_AGTPCNT1) AS AGNT_PCT_RT
    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
        )
    WHERE C.SP_AGTNMBR1 != '00000'
    
    UNION ALL
    
    -- 提取第二个代理人的数据
    SELECT
        TRIM(UPPER(M.SC_CNT_PREF)) || TRIM(UPPER(M.SC_CNT_NO)) || TRIM(UPPER(M.SC_CNT_SUF)) AS POLICY_NUMBER,
        'CL000' || C.SP_AGTNMBR2 AS AGENT_NUMBER,
        TO_NUMBER(C.SP_AGTPCNT2) AS AGNT_PCT_RT
    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
        )
    WHERE C.SP_AGTNMBR2 != '00000'
    
    UNION ALL
    
    -- 提取第三个代理人的数据
    SELECT
        TRIM(UPPER(M.SC_CNT_PREF)) || TRIM(UPPER(M.SC_CNT_NO)) || TRIM(UPPER(M.SC_CNT_SUF)) AS POLICY_NUMBER,
        'CL000' || C.SP_AGTNMBR3 AS AGENT_NUMBER,
        TO_NUMBER(C.SP_AGTPCNT3) AS AGNT_PCT_RT
    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
        )
    WHERE C.SP_AGTNMBR3 != '00000'
) t
ORDER BY POLICY_NUMBER;

格式适配说明 #

如果需要和示例输出的字符串格式完全一致，可将查询语句调整为：

SELECT
    POLICY_NUMBER || ' ---> ' || AGENT_NUMBER || ' ----> ' || AGNT_PCT_RT AS FORMATTED_OUTPUT
FROM (
    -- 内部子查询与上述一致
    ...
) t
ORDER BY POLICY_NUMBER;

内容的提问来源于stack exchange，提问作者karthik