How to Sort WM_CONCAT output 3

[Dagon]

I really like the WM_CONCAT function in 10g as a quick way of concatenating fields. However, it has one major drawback in that there is no way of ordering the fields in the string that it returns.

So for example:

create table test_sort (key1 number, data_val varchar2(10));

insert into test_sort values (1, 'Z');
insert into test_sort values (1, 'Y');
insert into test_sort values (1, 'X');
insert into test_sort values (2, 'B');
insert into test_sort values (2, 'A');

select key1, wm_concat(data_val)
from test_sort
group by key1;

      KEY1 OUTPUT         
---------- ---------------
         1 Z,Y,X          
         2 B,A       

You might prefer X,Y,Z and A,B but there is no way to guarantee this.

What I've discovered is that WM_CONCAT works like an analytic function in that it can take an ORDER BY clause. We can also apply count with an order by to find out how many items have been concatenated and a count without the order by to get the total number of concatenated items.

select key1, 
       wm_concat(data_val) over (partition by key1 order by data_val) as output, 
       count(data_val) over (partition by key1 order by data_val) as running_count, 
       count(data_val) over (partition by key1) as tot_count 
from test_sort

       KEY1 OUTPUT          RUNNING_COUNT  TOT_COUNT
---------- --------------- ------------- ----------
         1 X                           1          3
         1 X,Y                         2          3
         1 X,Y,Z                       3          3
         2 A                           1          2
         2 A,B                         2          2

Having got this output, all we then have to do is apply a condition running_count=tot_count and we get the sorted output.

select key1, output
from
(select key1, wm_concat(data_val) over (partition by key1 order by data_val) as output, 
         count(data_val) over (partition by key1 order by data_val) as running_count, 
         count(data_val) over (partition by key1) as tot_count 
from test_sort)
where running_count = tot_count

      KEY1 OUTPUT         
---------- ---------------
         1 X,Y,Z          
         2 A,B

For Oracle-related work, contact me through Linked-In.

 

[BJCooperIT]

Very nice! Thanks for taking the time to share. Have a star on me.

^{Beware of false knowledge; it is more dangerous than ignorance. ~ George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)

 

[karluk]

I guess I don't know enough about wm_concat, Dagon. Are you saying that the obvious attempts to sort the results are not guaranteed to work? For example, I tried the following select from an ordered subquery - and it worked for me. But I don't know if there could ever be circumstances where it might fail.

select key1, wm_concat(sorted_data_val) from
  (select key1, data_val as sorted_data_val from test_sort order by key1, sorted_data_val)
  group by key1

 

[Dagon]

It would probably work, but we don't know what the internal algorithms of wm_concat are, so without telling it explicitly to order the input, it may not always concatenate them in the order returned by the subquery. I prefer a solution in which I've told wm_concat to order them rather than assuming it will use the order returned by the query.

For Oracle-related work, contact me through Linked-In.