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 normalization and types of normalization?

22 Answers   Etisbew, F-TEC, Microsoft, TechProcess,

How do you modify a column in sql?

0 Answers  

counting the no.of characters occurs in a string by using pl/sql function

1 Answers   TCS,

What sql database should I use?

0 Answers  

What are different types of joins ?

5 Answers   BirlaSoft,

what is collation? : Sql dba

0 Answers  

Why do we use subquery?

0 Answers  

Table Student has 3 columns,Student_id,Student_Name & Course_Id. Table Course has 2 columns, Course_Id & Course_Name.Write a query to listdown all the Courses and number of student in each course.

10 Answers   TCS,

What is data control language (dcl)?

0 Answers  

What is user define exception and example

1 Answers   HP, KP,

What does (*) mean in sql?

0 Answers  

What does the file extension accdb stand for?

0 Answers