循环查询sql带逗号（，）分隔的数据 以及一对多数据转化为逗号（，）分割数据

逗号分割数据转化为一对多数据
select * from sys_role_list where id in
　　(select c from
　　(with test as (select roleid c from sys_role_info where id=1)
　　select substr(t.ca,instr(t.ca, ',', 1, c.lv) + 1,instr(t.ca, ',', 1, c.lv + 1) - (instr(t.ca, ',', 1, c.lv) + 1)) AS c
　　from (select ',' || c || ',' AS ca,length(c || ',') - nvl(length(REPLACE(c, ',')), 0) AS cnt FROM test) t,
    (select LEVEL lv from dual CONNECT BY LEVEL <= 100) c where c.lv <= t.cnt ))

　　转化为逗号分割数据

(select DoctID, deptname = (stuff((select ',' + deptname from HE_DoctDeptRelation  
  inner join yb_deptdict on yb_deptdict.DeptID=HE_DoctDeptRelation.DeptID 
  where DoctID =  a.DoctID for xml path('')),1,1,'')) from HE_DoctDeptRelation a group by DoctID) 
  as DoctDept