Answer Posted / 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

How can I delete duplicate rows?

1017

---

What does dml mean?

988

---

what is the difference between a having clause and a where clause? : Sql dba

1087

---

Can you sum a count in sql?

1011

---

Is sql similar to python?

976

---

Is inner join faster than left join?

1190

---

What is composite data type in pl sql?

1113

---

What is db journal file?

1067

---

What is coalesce in sql?

1060

---

What is mutating error in pl sql?

1020

---

What is fmtonly in sql?

1085

---

How do I sort a table in sql?

1047

---

What is database white box testing and black box testing?

1111

---

What are sql objects?

1081

---

Is trigger a stored procedure?

933

---