Oracle将一列值按照另一列分组拼接的方法讲解
程序员文章站
2022-05-03 20:29:06
怎么把一列值按照另一列分组拼成字符串。
比如下面两列:
sql> select deptno,ename from emp where deptno is not null;...
怎么把一列值按照另一列分组拼成字符串。
比如下面两列:
sql> select deptno,ename from emp where deptno is not null; 20 smith 30 allen 30 ward 20 jones 30 martin 30 blake 10 clark 20 scott 10 king 30 turner 20 adams 30 james 20 james 10 miller 14 rows selected.
将ename根据deptno的值拼成字符串。
实现方法一: 用分析函数row_number() over进行分组然后用sys_connect_by_path进行拼接:
select max(sys_connect_by_path(ename, ',')) name,deptno from (select ename, deptno, row_number() over(partition by deptno order by ename) ro from (select deptno,ename from emp where deptno is not null) ) newtab start with newtab.ro = 1 connect by prior newtab.ro = newtab.ro - 1 group by deptno;
结果为:
,clark,king,miller,scott,turner,ward 30 ,clark,king,miller,scott,smith 20 ,clark,king,miller 10
实现方法二: 更简便,用函数wm_concat(列名),该函数可以把列值以逗号分隔,并显示成一行。
sql> select deptno,wm_concat(ename) n from emp where deptno is not null group by deptno; deptno n ---------- -------------------------------------------------- 10 clark,miller,king 20 smith,james,adams,scott,jones 30 allen,james,turner,blake,martin,ward