wm_concat to listagg [message #674162] |
Mon, 07 January 2019 20:29 |
zxx2403
Messages: 13 Registered: November 2006 Location: china
|
Junior Member |
|
|
Hi All,
I want to know how to rewrite below SQL using listagg in oracle12c and above. Thanks
below SQL can run in 11g
select deptno,
max(sal) as max_sal,
max(ename) keep(dense_rank first order by sal desc) as max_ename,
sum(sal) keep(dense_rank first order by sal desc) as sum_ename,
count(sal) keep(dense_rank first order by sal desc) as count_ename,
to_char(wm_concat(ename) keep(dense_rank first order by sal desc)) as enames
from emp
group by deptno;
[mod-edit: code tags added by bb]
[Updated on: Mon, 07 January 2019 22:06] by Moderator Report message to a moderator
|
|
|
Re: wm_concat to listagg [message #674163 is a reply to message #674162] |
Mon, 07 January 2019 23:16 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
As far as I know, you cannot just substitute listagg for wm_concat and combine it with keep(dense_rank... directly, but you can work around it as shown below.
SCOTT@orcl_12.1.0.2.0> column enames format a10
SCOTT@orcl_12.1.0.2.0> select deptno,
2 max (sal) as max_sal,
3 max (ename) as max_ename,
4 sum (sal) as sum_ename,
5 count (sal) as count_ename,
6 listagg (ename, ',') within group (order by ename) as enames
7 from (select deptno, sal, ename,
8 dense_rank () over (partition by deptno order by sal desc) as dr
9 from emp)
10 where dr = 1
11 group by deptno
12 order by deptno;
DEPTNO MAX_SAL MAX_ENAME SUM_ENAME COUNT_ENAME ENAMES
---------- ---------- ---------- ---------- ----------- ----------
10 5000 KING 5000 1 KING
20 3000 SCOTT 6000 2 FORD,SCOTT
30 2850 BLAKE 2850 1 BLAKE
3 rows selected.
|
|
|