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:
|
|
 |
|
Guarantee your Success!
Oracle is the
world's most complex, robust and flexible database, considered
impossible to master without a mentor.
That's why all BC
Oracle trainers are working professionals, experts in Oracle who
share their tips and secrets. |
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright ? 1996 - 2012
All rights reserved.
Oracle ?
is the registered trademark of Oracle Corporation.
|
|
