select 課程名, wm_concat(學生姓名|| '、') 選課學生 from
(select * from
(select c.ci_id 課程名, s.stu_name 學生姓名,
instr(c.stu_ids, s.stu_id) choose
from pm_ci, pm_stu s
where instr(c.ci_id, s.stu_name) is not null)
where choose=1) group by 課程名;
select a.CI_ID,to_char(
(select wm_concat(decode(instr(a.stu_ids,b.stu_id),0,null,b.stu_name))
from pm_stu b
)
) STU_NAME
from pm_ci a where a.ci_id is not null;
select b1.cid,wm_concat(b1.sname) from
(select c.ci_id cid,s.stu_name sname,instr(c.stu_ids,s.stu_id)
from pm_ci c,pm_stu s where instr(c.stu_ids,s.stu_id)<>0) b1 group by b1.cid;
select p.ci_id,max(stu_name) as STU_NAME
from (select c.ci_id,wm_concat(s.stu_name)
over(partition BY c.stu_ids order by s.stu_id) as stu_name
from pm_ci c,pm_stu s
where instr(c.stu_ids,s.stu_id) > 0) p
group by p.ci_id;
