SELECT T.USER_NO,
T.USER_NAME,
MAX(SUBSTR(SYS_CONNECT_BY_PATH(T.ROLE_NAME, '/'), 2))
FROM (SELECT T.USER_NO,
T.USER_NAME,
T.ROLE_NAME,
T.USER_NO || ROW_NUMBER() OVER(PARTITION BY T.USER_NO ORDER BY T.USER_NO, LENGTH(T.ROLE_NAME)) RN,
T.USER_NO ||
(ROW_NUMBER() OVER(PARTITION BY T.USER_NO ORDER BY T.USER_NO,
LENGTH(T.ROLE_NAME)) + 1) RN_DOWN
FROM AAAAA T
) T
START WITH RN LIKE '%1'
CONNECT BY RN = PRIOR RN_DOWN
GROUP BY T.USER_NO, T.USER_NAME
ORDER BY T.USER_NO
---原本数据
---转换后的数据