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
本文转自海天一鸥博客园博客，原文链接：http://www.cnblogs.com/sgsoft/archive/2011/01/24/1943165.html，如需转载请自行联系原作者
