Home » SQL & PL/SQL » SQL & PL/SQL » wm_concat to listagg
wm_concat to listagg [message #674162] Mon, 07 January 2019 20:29 Go to next message
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 Go to previous message
Barbara Boehmer
Messages: 8911
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.
Previous Topic: Proxy Granted But Unable to Modified a Table owned by the Proxy Account
Next Topic: Determining a due date based on business hours
Goto Forum:
  


Current Time: Mon Mar 25 08:40:50 CDT 2019