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:
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_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
Another reader asks:
Question: I have some questions about
using the wm_concat function. I have been told that I
can use wm_concat to concatinate some data, but I ran into
some issues. Using wm_concat returned some errors
about unexpected data types, so I added a
CAST statement
to make everything VARCHAR:
cast ( ... as varchar2(...))
Is there a need to use SUBSTR as well?
- If the result of the wm_concat is 100 chars, can I
use the following to truncate the results to 15 characters just
like substr(wm_concat(...),1,15)?
cast(wm_concat(...) as varchar2(15))
- Assuming the result of the wm_concat is 15 chars
and I wanted to CAST it to 4000 chars, would the result be 15
characters or would I get a bunch of white space to go with it?
Answer: Oracle Guru Laurent Schneider has
this to add about the use of CAST and wm_concat:
Regarding the results returned by wm_concat, The use of
CAST does indeed resize or expand to the specified length. As for
using varchar2 with the wm_concat results, it does not
right pad with blank space. An example of the use of CAST:
select
'abcdef',
cast('abcdef'
as varchar2(3)) v3,
length(cast('abcdef' as varchar2(3))
) lv3,
cast('abcdef' as varchar2(9)) v9,
length(cast('abcdef'
as varchar2(9)) ) lv9
from dual;
'ABCDEF' V3 LV3
V9 LV9
-------- --- ---------- ---------
----------
abcdef abc 3 abcdef 6
Casting to a larger string is more visible in a view:
SQL> create view v as
2 select
cast('abcdef' as varchar2(9)) vc9, substr('abcdef',1,9) sub from
dual;
View created.
SQL> desc v
Name Null? Type
----------------- --------
------------
VC9 VARCHAR2(9)
SUB VARCHAR2(6)
Do not use CAST if you need SUBSTR(char), TO_CHAR(number) or
TO_CHAR(date) !
It is important to note that wm_concat is an
undocumented and unsupported function and should be used wisely.
Its use should be thoroughly tested in a development environment
before use in production.
Starting in Oracle 11gR2,
LISTAGG is preferred over the unsupported wm_concat
function.
Also see:
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
