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