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_IDSTU_NAME1张三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_IDNAMESLIST1张三,赵六,王五,李四2张三,赵六