SQL wm_concat function

SQL wm_concat function Question:

I have a table test_test and I need to count the distinct mark columns and them display all matching values on one line:

Name    Mark
------- ------
ABC     10
DEF     10
GHI     10
JKL     20
MNO     20
PQR     30

The result should be like this, with the count and the rows groups onto the same line;
 
mark count     names
---- -----     -----------
10       3     ABC,DEF,GHI
20       2     JKL,MNO
30       1     PQR

Answer:  You could write your own aggregate function or use wm_concat:

select 
   mark, 
   count(*), 
   wm_concat(name) 
from 
   test_test 
group by 
   mark;

Here is another example of using wm_concat:

select 
   deptno, 
   wm_concat(distinct ename) 
from 
   emp 
group by 
   deptno;

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