ORACLE wm_concat (wmsys.) 函数(行列转换函数)

SQL wm_concat function
Expert Oracle Tips by Burleson Consulting

September 19, 2010

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 tghis, 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: By Laurent Schneider:  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_contcat:

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

Also see:

pivoting one row of several columns into one column of several rows.
Display and sort output on a single line

 

SQL>                                   
SQL> select mgr ,                      
  2  wm_concat(ename) names,           
  3  count(*)CNT                       
  4  from scott.emp                    
  5  group by mgr                      
  6  ;                                 
                                       
  MGR NAMES                              CNT
----- -------------------------------  -----
 7566 SCOTT,FORD                           2
 7698 ALLEN,WARD,TURNER,JAMES,MARTIN       5
 7782 MILLER                               1
 7788 ADAMS                                1
 7839 JONES,CLARK,BLAKE                    3
 7902 SMITH                                1
      KING                                 1
                                       
7 rows selected