站点图标 IDC铺

关于oracle多行数所转换为用,号拼接成一行数据的SQL例子

方法1、SELECT a,replace(wmsys.wm_concat(b),’,’,”) FROM 表名 t GROUP BY a;

方法2、select deptno,ename_path from (select deptno,ename_path,

row_number() over(partition by deptno order by deptno,curr_level desc) ename_path_rank

from (select deptno,ename,rank,level as curr_level,

ltrim(sys_connect_by_path(ename,’,’),’,’) ename_path from (

select deptno,ename,row_number() over(partition by deptno order by deptno,ename) rank

from emp order by deptno,ename) connect by deptno = prior deptno and rank-1 = prior rank))

where ename_path_rank=1;

退出移动版