oracle合并列的函数wm_concat的使用详解

oracle wm_concat(column)函数实现字段合并

例：

表

 create table pm_ci
 (ci_id varchar(20) primary key,
  stu_ids varchar(100));

insert into pm_ci values('1','1,2,3,4');
insert into pm_ci values('2','1,4');

 create table pm_stu
 (stu_id varchar(20) primary key,
  stu_name varchar(20));
insert into pm_stu values('1','张三');
insert into pm_stu values('2','李四');
insert into pm_stu values('3','王五');
insert into pm_stu values('4','赵六');
commit;

select a.ci_id,b.stu_name
from pm_ci a,pm_stu b
where instr(a.stu_ids,b.stu_id) > 0;

CI_ID	STU_NAME
1	张三
1	李四
1	王五
1	赵六
2	张三
2	赵六

使用oracle wm_concat(column)函数实现：

select ci_id,wm_concat(stu_name) nameslist
from(select a.ci_id,b.stu_name
from pm_ci a,pm_stu b
where instr(a.stu_ids,b.stu_id) > 0)
group by ci_id;

CI_ID	NAMESLIST
1	张三,赵六,王五,李四
2	张三,赵六