wm_concat 多行字符串拼接

一、wm_concat 多行字符串拼接
有如下员工部门表emp_dept,数据如下:

需要实现如下结果

就需要用到wm_concat 函数:
sql如下:

select dept_name 部门, wm_concat(t.emp_name) 员工 from emp_dept t group by dept_name
1
2
但是这样的查询结果却是:


这里还需要to_char一下

select dept_name 部门, to_char(wm_concat(t.emp_name))员工 from emp_dept t group by dept_name
1
2
如果需要将拼接的字符串改为 ‘;’

select dept_name 部门, replace(to_char(wm_concat(t.emp_name)),',',';')员工 from emp_dept t group by dept_name
1
2
执行结果:

二、字符串拆分为多行
有字符串 ‘a,b,c,d,e’需要拆分为:

sql如下:

SELECT regexp_substr('a,b,c,d,e', '[^,]+', 1, rownum) 字符串拆分
from dual
CONNECT BY rownum <= length(regexp_replace('a,b,c,d,e', '[^,]', NULL)) + 1;
1
2
3
同样我们可以将销售部的张三,王五,李四进行拆分

with tmp as
(select to_char(wm_concat(t.emp_name)) emp_name
from emp_dept t
where t.dept_name = '销售部'
group by dept_name)
SELECT regexp_substr(tmp.emp_name, '[^,]+', 1, rownum) 员工
FROM tmp
CONNECT BY rownum <= length(regexp_replace(tmp.emp_name, '[^,]', NULL)) + 1;
执行结果如下:
1
2
3
4
5
6
7
8
9

到这里,可能有人会像可不可以将一中wm_concat 多行拼接字符串的结果全部拆分,答案是肯定,但是用sql语句是无法实现的,需要使用存储过程,将多行拼接的字符串循环传入到上面sql中进行拆分。有兴趣的童鞋可以尝试下,采用游标是最好的选择。
附上存储过程代码

create or replace procedure tmp_split is
--声明多行拼接字符串查询游标
cursor cur_string_concat is
select to_char(wm_concat(t.emp_name)) emp_name
from emp_dept t
group by dept_name;
--声明变量rec_emp_name表示为游标cur_string_concat一行记录
rec_emp_name cur_string_concat%rowtype;
--声明拆分字符串游标,传入参数cur_string为需要拆分的字符串
cursor cur_string_split(cur_string varchar2) is
SELECT regexp_substr(cur_string, '[^,]+', 1, rownum) e_name
FROM dual
CONNECT BY rownum <=
length(regexp_replace(cur_string, '[^,]', NULL)) + 1;
--声明变量rec_string游标cur_string_split的一行记录
rec_string cur_string_split%rowtype;
begin
--先打开字符串拼接游标
open cur_string_concat;
--开始循环
loop
--将cur_string_concat每次循环的结果赋值给变量rec_emp_name
fetch cur_string_concat
into rec_emp_name;
--当cur_string_concat迭代完了退出
exit when cur_string_concat%notfound;
open cur_string_split(rec_emp_name.emp_name);
loop
fetch cur_string_split
into rec_string;
exit when cur_string_split%notfound;
--将拆分的结果保存到表中
insert into tmp_split_emp
select rec_string.e_name from dual t;
end loop;
--一定要关闭游标
close cur_string_split;
end loop;
commit;
close cur_string_concat;

end tmp_split;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
执行存储过程:

begin
tmp_split;
end ;
1
2
3
查询结果为:

————————————————
版权声明:本文为CSDN博主「菜鸟的梦想ff」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/whandgdh/article/details/80681493

posted @   silentmuh  阅读(632)  评论(0编辑  收藏  举报
Live2D
欢迎阅读『wm_concat 多行字符串拼接』
  1. 1 Walk Thru Fire Vicetone
  2. 2 爱你 王心凌
  3. 3 Inspire Capo Productions - Serenity
  4. 4 Welcome Home Radical Face
  5. 5 粉红色的回忆 李玲玉
Welcome Home - Radical Face
00:00 / 00:00
An audio error has occurred, player will skip forward in 2 seconds.

作词 : Ben P. Cooper

作曲 : Cooper

Sleep don't visit, so I choke on sun

And the days blur into one

And the backs of my eyes hum with things I've never done

Sheets are swaying from an old clothesline

Was never much but we've made the most

Welcome home

Ships are launching from my chest

Some have names but most do not

If you find one,please let me know what piece I've lost

Heal the scars from off my back

I don't need them anymore

You can throw them out or keep them in your mason jars

I've come home

All my nightmares escaped my head

Bar the door, please don't let them in

You were never supposed to leave

Now my head's splitting at the seams

And I don't know if I can

Here, beneath my lungs

I feel your thumbs press into my skin again

点击右上角即可分享
微信分享提示