请问一下,有这样的分组函数吗? oracle中,select sum(col1) from ... 可以将col1加起来,但是对于字符串来说,有没有一个函数能将字符串串联拼接起来呢?
比如:
id col1
------ --------
1 aaa
1 bbb
1 ccc
2 oooselect id, XXF(col1) from ... group by id结果是:
id col1
------ --------
1 aaabbbccc
2 ooo
比如:
id col1
------ --------
1 aaa
1 bbb
1 ccc
2 oooselect id, XXF(col1) from ... group by id结果是:
id col1
------ --------
1 aaabbbccc
2 ooo
create table t( id int,col1 varchar2(100));
insert into t
select 1,'aaa' from dual union all
select 1,'bbb' from dual union all
select 1,'ccc' from dual union all
select 2,'ooo' from dual;
--执行查询
select id, replace(substr(max(sys_connect_by_path(col1, ' ')), 2),' ','') catstr
from (select col1, id, row_number() over(partition by id order by 1) rn
from t)
start with rn = 1
connect by rn - 1 = prior rn and id = prior id
group by id;
--输出结果
1 aaabbbccc
2 ooo