oracle 将一对多查询结果放入一条记录

http://seandeng888.iteye.com/blog/2110590 学习了


需求 查询用户对应的所有角色

用户A  角色1，角色2....

SELECT USER_CODE, LTRIM(MAX(sys_connect_by_path(ROLE_NAME, ',')), ',') VALS
  from (select U.USER_CODE USER_CODE,
               R.ROLE_NAME ROLE_NAME,
               row_number() over(partition by USER_CODE order by USER_CODE) rn
          FROM TB_EMM_USER U, TB_EMM_USER_ROLE UR, TB_EMM_ROLE R
          WHERE U.USER_ID = UR.USER_ID
           AND UR.ROLE_ID = R.ID)
 start with rn = 1
connect by rn - 1 = prior rn
       AND USER_CODE = PRIOR USER_CODE
 group by USER_CODE
 order by USER_CODE