Oracle中如何将表内逗号分隔的ID转换为对应姓名并拼接返回

首先约定演示用表结构：

• 人员映射表：person，字段ID（人员ID，数值类型）、NAME（人员姓名，字符串类型）
• 家族信息表：family，字段FAMILY_NAME（家族名称，字符串类型）、BROTHERS（兄弟ID逗号分隔字符串）、SISTERS（姐妹ID逗号分隔字符串）

推荐写法（兼容Oracle 12c及以上版本） #

用XMLTABLE拆分字符串，逻辑简洁不易产生重复数据：

SELECT
    f.family_name,
    -- 转换兄弟ID为姓名拼接
    (
        SELECT LISTAGG(p.name, ',') WITHIN GROUP (ORDER BY TO_NUMBER(t.id_val))
        FROM family f1,
             XMLTABLE(('"' || REPLACE(f1.brothers, ',', '","') || '"')) COLUMNS id_val VARCHAR2(10) PATH '.'
        JOIN person p ON p.id = TO_NUMBER(t.id_val)
        WHERE f1.family_name = f.family_name
    ) AS brothers,
    -- 转换姐妹ID为姓名拼接
    (
        SELECT LISTAGG(p.name, ',') WITHIN GROUP (ORDER BY TO_NUMBER(t.id_val))
        FROM family f2,
             XMLTABLE(('"' || REPLACE(f2.sisters, ',', '","') || '"')) COLUMNS id_val VARCHAR2(10) PATH '.'
        JOIN person p ON p.id = TO_NUMBER(t.id_val)
        WHERE f2.family_name = f.family_name
    ) AS sisters
FROM family f
WHERE f.family_name = 'Stark';

兼容低版本Oracle写法（支持11g及以上） #

用CONNECT BY拆分字符串，适配老版本环境：

WITH stark_base AS (
    -- 先过滤出Stark家族的基础数据
    SELECT family_name, brothers, sisters FROM family WHERE family_name = 'Stark'
),
-- 拆分兄弟ID为多行
brother_split AS (
    SELECT DISTINCT REGEXP_SUBSTR(brothers, '[^,]+', 1, LEVEL) AS id
    FROM stark_base
    CONNECT BY REGEXP_SUBSTR(brothers, '[^,]+', 1, LEVEL) IS NOT NULL
),
-- 拆分姐妹ID为多行
sister_split AS (
    SELECT DISTINCT REGEXP_SUBSTR(sisters, '[^,]+', 1, LEVEL) AS id
    FROM stark_base
    CONNECT BY REGEXP_SUBSTR(sisters, '[^,]+', 1, LEVEL) IS NOT NULL
),
-- 拼接兄弟姓名
brother_agg AS (
    SELECT LISTAGG(p.name, ',') WITHIN GROUP (ORDER BY TO_NUMBER(bs.id)) AS brothers
    FROM brother_split bs
    JOIN person p ON p.id = TO_NUMBER(bs.id)
),
-- 拼接姐妹姓名
sister_agg AS (
    SELECT LISTAGG(p.name, ',') WITHIN GROUP (ORDER BY TO_NUMBER(ss.id)) AS sisters
    FROM sister_split ss
    JOIN person p ON p.id = TO_NUMBER(ss.id)
)
SELECT 
    sb.family_name,
    ba.brothers,
    sa.sisters
FROM stark_base sb, brother_agg ba, sister_agg sa;

逻辑说明 #

• 第一步将逗号分隔的ID字符串拆分为多行单个ID值
• 拆分后的ID关联人员映射表获取对应的姓名，ORDER BY TO_NUMBER(id)保证姓名顺序和原始ID顺序一致
• 用LISTAGG聚合函数将多行姓名重新拼接为逗号分隔的单行字符串

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