方法一：listagg ， 参考链接 ，从oracle11g后出现的新函数

如果拼接的字符串长度超过4000字节，会报 o ra-01489错误， ora-01489 字符串连接的结果过长 解决方案 。

SELECT 
	id,count(name) as countname,
	LISTAGG(to_char(name), ',') WITHIN GROUP(ORDER BY name) AS pjname
	select 
		distinct B.id as id, 
		B.name as name
		B left join C on B.name = C.name
	where 
		C.gender = 'female'
group by id
　　方法二：wmsys.wm_concat，参考链接，返回结果类型会受版本影响，不建议使用
select 
	grp, wmsys.wm_concat(str)
	select 1 grp, 'a1' str from dual
    union
    select 1 grp, 'a2' str from dual
    union
    select 2 grp, 'b1' str from dual
    union
    select 2 grp, 'b2' str from dual
    union
    select 2 grp, 'b3' str from dual
group by
　　方法三（1）：sys_connect_by_path，参考链接
select role_category,  
       ltrim(sys_connect_by_path(role_id, ','), ',') as role_id_list  
from   (  
        select role_category,  
               role_id,  
               (row_number() over(partition by role_category order by role_id))  
                  as row_number  
        from   (  
                select 'role_01' as role_id ,'admin' as role_category from dual  
                union all  
                select 'role_02' as role_id ,'admin' as role_category from dual  
                union all  
                select 'role_03' as role_id ,'normal' as role_category from dual  
                union all  
                select 'role_04' as role_id ,'normal' as role_category from dual  
                union all  
                select 'role_05' as role_id ,'normal' as role_category from dual  
                union all  
                select 'role_06' as role_id ,'normal' as role_category from dual  
                union all  
                select 'role_07' as role_id ,'normal' as role_category from dual  
                union all  
                select 'role_08' as role_id ,'normal' as role_category from dual  
where  connect_by_isleaf = 1  
       start with row_number = 1  
       connect by row_number - 1 = prior row_number  
       and role_category = prior role_category
　　方法三（2）：sys_connect_by_path，参考链接
select 
	deptno,
	ltrim(max(sys_connect_by_path(ename, ',')), ',') name
	select 
		deptno, 
		ename, 
		row_number() over(partition by deptno order by ename) ro
		select 
			deptno,ename
		where 
			deptno is not null
) newtab
start with newtab.ro = 1
connect by prior newtab.ro = newtab.ro - 1
group by deptno;
　　方法三（3）：sys_connect_by_path，参考链接
select 
	deptno,
	ltrim(max(sys_connect_by_path(ename, ',')), ',') name
	select 
		deptno, 
		ename, 
		lead(ro) over(partition by deptno order by ro) ro_next
		select 
			deptno, ename,
			row_number() over(order by deptno, ename) ro
		where 
			deptno is not null
) newtab
start with ro_next is null
connect by ro_next = prior ro
group by deptno;