SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

wm_concat and order

719215Nov 3 2009 — edited Nov 4 2009

When ran the following query, I got the enames with unordered result . Does anyone know how to get the wm_concat column to order\sort ? I use oracle v10 . Any thoughts are appreciated.Thanks.

select distinct deptno ,wm_concat(ename) over ( partition by deptno order by deptno ) as enames
from emp
group by deptno,ename
order by deptno

current output:

DEPTNO ENAMES 
10	MILLER,CLARK,KING 
20 	SCOTT,JONES,FORD,ADAMS,SMITH 
30	MARTIN,JAMES,WARD,ALLEN,TURNER,BLAKE

Target output:

DEPTNO ENAMES 
10	CLARK,KING,MILLER 
20 	ADAMS,FORD,JONES,SCOTT,SMITH 
30	ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

Wanwan

This post has been answered by Karthick2003 on Nov 3 2009

Jump to Answer

Karthick2003

Answer

wm_concat is undocumented. so please dont use that. you can use the below query. it also solves your order by problem.

select deptno, ltrim(sys_connect_by_path(ename,','),',')
  from (select deptno, ename, row_number() over(partition by deptno order by ename) rno
          from emp)
where connect_by_isleaf = 1
connect by deptno = prior deptno
  and rno = prior rno+1
start with rno = 1

Marked as Answer by 719215 · Sep 27 2020

AlanWms

Have you tried?

order by deptno, ename

728534

I ebleive not suported
Unless you trick it somehow
These LInks may help for further investigation

425510

3656084

Cheers!!!
Bhushan

Anurag Tibrewal

Hi,

select distinct deptno ,wm_concat(ename) over ( partition by deptno order by ename) as enames
from emp
group by deptno,ename
order by deptno

select deptno, wm_concat(ename) from
(select deptno,ename from emp order by deptno, ename)
group by deptno

Regards
Anurag Tibrewal

Centinul

Anurag Tibrewal wrote:
Hi,

select distinct deptno ,wm_concat(ename) over ( partition by deptno order by ename) as enames
from emp
group by deptno,ename
order by deptno

This returns multiple rows per department.

>

select deptno, wm_concat(ename) from
(select deptno,ename from emp order by deptno, ename)
group by deptno

This doesn't return the results you are expecting. The names aren't in order.

SQL> select deptno, wm_concat(ename) as  ename from
  2  (select deptno,ename from scott.emp order by deptno, ename)
  3  group by deptno
  4
SQL> /

    DEPTNO ENAME
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,SMITH,SCOTT,FORD,JONES
        30 ALLEN,JAMES,TURNER,WARD,MARTIN,BLAKE

Hoek

SQL> with emp as (  -- generating testdata:
  2  select 10 deptno, 'MILLER' ename from dual union all
  3  select 10, 'CLARK' from dual union all
  4  select 10, 'KING' from dual union all 
  5  select 20, 'SCOTT' from dual union all
  6  select 20, 'JONES' from dual union all
  7  select 20, 'FORD' from dual union all
  8  select 20, 'ADAMS' from dual union all
  9  select 20, 'SMITH ' from dual union all
 10  select 30, 'MARTIN' from dual union all
 11  select 30, 'JAMES' from dual union all
 12  select 30, 'WARD' from dual union all
 13  select 30, 'ALLEN' from dual union all
 14  select 30, 'TURNER' from dual union all
 15  select 30, 'BLAKE' from dual
 16  )
 17  --
 18  -- actual query:
 19  --
 20  select deptno
 21  ,      enames
 22  from ( select deptno
 23         ,      wm_concat(ename) over ( partition by deptno order by ename) as enames
 24         ,      row_number() over ( partition by deptno order by ename desc) rn
 25         from   emp
 26         group by deptno,ename
 27         order by deptno
 28        )
 29  where rn=1;

    DEPTNO ENAMES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

One of the weirdest queries ever that crossed my mind ;)

edit

Stumbled upon this accidentally, but it's a nice yal anyway:
http://www.williamrobertson.net/documents/one-row.html

Edited by: hoek on Nov 3, 2009 3:12 PM

719215

Thank you for all your support. I was aware that wm_concat is undocumented but in my opinion this one is way easier to understand than the rest. I think V11 comes with a new feature (listagg ?) that can make this a breeze. Too bad I am not using V11 yet. It is only fair to give the 1st one answered.

Thanks Karthick for your quick fingers. I am out to read up the ltrim and sys_connect_by_path. Hope they will make sense to me. My real problem has 10+ columns to group .

728534

*It is only fair to give the 1st one answered*

we ar ehappy you atleast considered our answers ;)

Cheers!!!
Bhushan

719215

What if I wnat to concat salary column in additin to deptno ? The following code only gives me same deptno each. Can you help ? got to be some grouping but I am not getting it . Thanks.

select deptno, ltrim(sys_connect_by_path(ename,','),',')
from (select deptno, ename, row_number() over(partition by deptno order by ename) rno
from emp)
where connect_by_isleaf = 1
connect by deptno = prior deptno
and rno = prior rno+1
start with rno = 1

select deptno, ltrim(sys_connect_by_path(SAL,','),',')
from (select deptno, SAL, row_number() over(partition by deptno order by SAL) rno
from emp)
where connect_by_isleaf = 1
connect by deptno = prior deptno
and rno = prior rno+1
start with rno = 1

DEPTNO ENAMES 	
10	1300,2450,5000				
10	CLARK,KING,MILLER 
20	800,1100,2975,3000,3000			
20 	ADAMS,FORD,JONES,SCOTT,SMITH 
30	950,1250,1250,1500,1600,2850		
30	ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

target output:

DEPTNO ENAMES 					SAL
10	CLARK,KING,MILLER 			1300,2450,5000
20 	ADAMS,FORD,JONES,SCOTT,SMITH 		800,1100,2975,3000,3000
30	ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD	950,1250,1250,1500,1600,2850

Hoek

Do you mean:

SQL> with emp as ( -- generating sample dataset:
  2  select 10 deptno, 'MILLER' ename, 1300 salary from dual union all
  3  select 10, 'CLARK'  ,2450 from dual union all
  4  select 10, 'KING'   ,5000 from dual union all
  5  select 20, 'SCOTT'  ,800  from dual union all
  6  select 20, 'JONES'  ,1100 from dual union all
  7  select 20, 'FORD'   ,2975 from dual union all
  8  select 20, 'ADAMS'  ,3000 from dual union all
  9  select 20, 'SMITH ' ,3000 from dual union all
 10  select 30, 'MARTIN' ,950  from dual union all
 11  select 30, 'JAMES'  ,1250 from dual union all
 12  select 30, 'WARD'   ,1250 from dual union all
 13  select 30, 'ALLEN'  ,1500 from dual union all
 14  select 30, 'TURNER' ,1600 from dual union all
 15  select 30, 'BLAKE'  ,2850 from dual
 16  )
 17  --
 18  -- actual query:
 19  --
 20  select deptno
 21  ,      ltrim(sys_connect_by_path(ename,','),',') enames
 22  ,      ltrim(sys_connect_by_path(salary,','),',') sal
 23  from ( select deptno
 24         ,      ename
 25         ,      salary
 26         ,      row_number() over ( partition by deptno order by ename ) rn
 27         from emp
 28        )
 29  where connect_by_isleaf = 1
 30  start with rn = 1
 31  connect by deptno = prior deptno
 32         and rn = prior rn+1;

    DEPTNO ENAMES                                             SAL
---------- -------------------------------------------------- ------------------------------
        10 CLARK,KING,MILLER                                  2450,5000,1300
        20 ADAMS,FORD,JONES,SCOTT,SMITH                       3000,2975,1100,800,3000
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD               1500,2850,1250,950,1600,1250

(Keep in mind that my dataset differs a bit from yours)

hbvenki

Hi,
If u want to concatenate for regular on different task, you can use this.

CREATE OR REPLACE FUNCTION concatenate_list (p_cursor IN SYS_REFCURSOR)
RETURN VARCHAR2
IS
l_return VARCHAR2(32767);
l_temp VARCHAR2(32767);
BEGIN
LOOP
FETCH p_cursor
INTO l_temp;
EXIT WHEN p_cursor%NOTFOUND;
l_return := l_return || ',' || l_temp;
END LOOP;
RETURN LTRIM(l_return, ',');
END;

SELECT e1.deptno,
concatenate_list(CURSOR(SELECT e2.ename FROM emp e2 WHERE e2.deptno = e1.deptno)) employees
FROM emp e1
GROUP BY e1.deptno;

DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

hb venki
http://venki-hb.blogspot.com/

Hoek

Hi venkatesh,

Keep in mind that OP wants an ordered concatenated list.

hbvenki

Hi hoek, i just copied for his reference on different way, but when you use order by on both place then will get there requirement.

SELECT e1.deptno,
concatenate_list(CURSOR(SELECT e2.ename FROM emp e2 WHERE e2.deptno = e1.deptno order by 1)) em
FROM emp e1
GROUP BY e1.deptno
order by 1

hb venki
http://venki-hb.blogspot.com/

719215

Hb,

Thanks for the brilliant idea. This is very good stuff. I was able to duplicate your idea and include both (ename, sal) on the report. However, I realized if I order by ename then the corresponding Sal will be messed up. ie. Sal doesn't match the emp name. Is there a way to remedy this ? Thanks in advance.

 (correct sal with respect to em. but em is not in order )
DEPTNO	 EM 					SAL 
10 	KING,CLARK,MILLER 			5000,2450,1300 
20	JONES,SCOTT,FORD,SMITH,ADAMS 		2975,3000,3000,800,1100 
30 	BLAKE,ALLEN,WARD,MARTIN,TURNER,JAMES 	2850,1600,1250,1250,1500,950

(correct em order but SAL is not corresponding to em)
DEPTNO 		EM 					SAL 
10 		CLARK,KING,MILLER 			1300,2450,5000 
20 		ADAMS,FORD,JONES,SCOTT,SMITH 		800,1100,2975,3000,3000 
30 		ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD 	950,1250,1250,1500,1600,2850

Hoek

You have been provided the answer to this question already, also by Venkatesh.
Please reread your own thread and understand the answers.
No effort = no gain.
If that still doesn't work, then post your actual queries.

719215

Fair Enough. Thanks.

1 - 16

Locked Post

New comments cannot be posted to this locked post.

Locked on Dec 2 2009

Added on Nov 3 2009

16 comments

5,713 views

SQL & PL/SQL

wm_concat and order

Comments

Post Details