Oracle中合并字符串总结

在数据库中经常要合并字符串，而合并字符串的方法有很多，现在总结如下：

--创建会话级临时表
create global temporary table TMPA
(
  ID   INTEGER,
  NAME VARCHAR2(10)
)
on commit preserve rows;

 

--插入记录
insert into tmpa select 1,'aa' from dual;
insert into tmpa select 1,'bb' from dual;
insert into tmpa select 1,'cc' from dual;
insert into tmpa select 2,'dd' from dual;
insert into tmpa select 2,'ee' from dual;
insert into tmpa select 3,'ff' from dual;
commit;

 

--1、sys_conect_by_path方法

select id,max(ltrim(sys_connect_by_path(name,','),',')) as group_name
from
(
select a.*,row_number()over(partition by id order by name) as row_num
from tmpa a
) a
group by id
start with row_num=1
connect by prior id=id and prior row_num= row_num-1

 

--2、wm_concat方法

select id,wm_concat(name) as group_name from tmpa
group by id;

 

--3、自定义函数法

--定义函数

create function group_concat(vid number)
return varchar2
as
vResult varchar2(100);
begin
for cur in (select name from tmpa where id=vid) loop
vResult := vResult||cur.name;
end loop;
return vResult;
end;

--查询

select id,group_concat(id) as group_name from tmpa
group by id;

 

--查询的结果

ID GROUP_NAME
1  aa,bb,cc
2  dd,ee
3  ff