The LISTAGG analytic function was introduced in Oracle 11g Release 2
SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP BY deptno;
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
If you are not running 11g Release 2 (undocumented)
SELECT deptno, wm_concat(ename) AS employees
FROM emp
GROUP BY deptno;
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
Check it out here: http://www.oracle-base.com:80/articles/misc/StringAggregationTechniques.php
Related Links:
- LISTAGG - ON OVERFLOW TRUNCATE - Oracle 12c R2
- Function to Compare two Comma Separated Strings in Oracle
- Oracle: Difference between wm_concat and ListAgg
- Oracle: Comma Separated String To Rows 11g
- Oracle: Comma Separated String To Rows 10g Onwards
- Oracle: Nth Record from Comma Separated String
- Oracle: Department wise Employee Ids Comma Separated
- Oracle: Comma Separated String To Rows
SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP BY deptno;
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
If you are not running 11g Release 2 (undocumented)
SELECT deptno, wm_concat(ename) AS employees
FROM emp
GROUP BY deptno;
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
Check it out here: http://www.oracle-base.com:80/articles/misc/StringAggregationTechniques.php
Related Links:
- LISTAGG - ON OVERFLOW TRUNCATE - Oracle 12c R2
- Function to Compare two Comma Separated Strings in Oracle
- Oracle: Difference between wm_concat and ListAgg
- Oracle: Comma Separated String To Rows 11g
- Oracle: Comma Separated String To Rows 10g Onwards
- Oracle: Nth Record from Comma Separated String
- Oracle: Department wise Employee Ids Comma Separated
- Oracle: Comma Separated String To Rows
good job!
ReplyDeletethanx :)
ReplyDeleteWell Done!
ReplyDeleteNice post
ReplyDeleteWhy you haven't taken blog name as any keyword. with the help of keyword you can generate more traffic.
ReplyDeleteYou can make you blog keyword specific so that chances of getting more traffic and returning also. visitor Nicely presented information in this post, I prefer to read this kind of stuff. The quality of content is fine and the conclusion is good. Thanks for the post.
ReplyDeletejust my name, my blog :)
ReplyDeleteThanks a lot man..
ReplyDelete