oracle id拼接转换成对应中文拼接

WITH ExplodedIDs AS (
    SELECT
        planid, -- 主键或标识符
        REGEXP_SUBSTR(jcgroup, '[^,]+', 1, LEVEL) AS single_id
    FROM jc_plan
    CONNECT BY LEVEL <= REGEXP_COUNT(jcgroup, '[^,]+')
),
DistinctIDs AS (
    SELECT
        planid,
        single_id
    FROM ExplodedIDs
    GROUP BY planid, single_id -- 这里使用 GROUP BY 来去除每个 planid 下重复的 single_id
),
Uniquegroupnames AS (
    SELECT
        DistinctIDs.planid,
        jc_group.groupname
    FROM DistinctIDs
    LEFT JOIN jc_group ON DistinctIDs.single_id = jc_group.groupid
)
SELECT
    planid,
    LISTAGG(groupname, ', ') WITHIN GROUP (ORDER BY groupname) AS groupnames
FROM Uniquegroupnames
GROUP BY planid;