Answer / pankaj goyal

SQL wm_concat function
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_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

Answer / ajit

select Replace ( Wm_concat( a ), ',' ) Actout
from Tablename;

Answer / ramaraju

select listagg(cust,' ') within group(order by cust) from t2;

Answer / prativa mishra

select xmlagg(xmlelement(g,column_name)).extract('//text()')
from table_name

Answer / kalaiselvi

select name from table_name order by name

What is a sql select statement?

0 Answers  

What is sql and how does it work?

0 Answers  

Is it possible to access the current value in a session before accessing next value?

1 Answers  

Does db2 use sql?

0 Answers  

what is bdb (berkeleydb)? : Sql dba

0 Answers  

in materialized view the structure will create immediately or not?

2 Answers   iFlex,

What is a procedure in pl sql?

0 Answers  

what are the different type of normalization? : Sql dba

0 Answers  

declare lowerl number:= 1; upperl number:= 3; num varchar2(10); begin for i into lowerl..upperl loop num:=num||to_char(lowerl); if i=3 then upperl:=5; end loop; message(num); What will be the output ?

3 Answers   Oracle,

What is the difference between jpql and sql?

0 Answers  

what is the difference between clustered and non clustered index in sql? : Sql dba

0 Answers  

can we write stored function in out parameters? how to call through select statement? how to written more than one value plz give the exmple?

1 Answers