oracle行列字符串

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

 

---原本数据

---转换后的数据