原始数据 USER_IDS和USER_NAMES逗号拼接
-- 分割逗号 拼接得数据 转成多行
WITH TEMP AS
(SELECT
RSG.ID,
RSG.USER_IDS,
RSG.USER_NAMES,
ROWNUM ROWNUM1
FROM TABLE_NAME RSG
)
SELECT
T.ID,
REGEXP_SUBSTR(T.USER_NAMES, '[^,]+', 1, LEVEL) USER_NAME,
REGEXP_SUBSTR(T.USER_IDS, '[^,]+', 1, LEVEL) USER_ID
FROM TEMP T
CONNECT BY PRIOR ROWNUM1 = ROWNUM1
AND LEVEL <= REGEXP_COUNT(T.USER_IDS, '[^,]+')
AND PRIOR DBMS_RANDOM.VALUE() IS NOT NULL;
结果