String Aggregation in Oracle

String Aggregation in Oracle

Aggregation is concatenation of values from more than one row. In Oracle we can achieve this aggregation in 3 ways.
For example, we need get all employees for each department at single row.

1. Specific Function
Here we can create a function for concatenation, call the function for each department. The function contains for…loop, it may takes more times when the table contains huge data.

CREATE OR REPLACE FUNCTION func_testStrAggt
(
nDEPTNO in EMP1.DEPTNO1%TYPE
)
RETURN VARCHAR2
IS
strRet varchar2(32000) := '';
BEGIN
FOR cur_rec IN (SELECT ENAME FROM EMP1 WHERE DEPTNO1 = nDEPTNO)
LOOP
strRet := strRet || cur_rec.ENAME || ' ,';
END LOOP;
RETURN strRet;
END;

SELECT DEPTNO1, func_testStrAggt(DEPTNO1) AS EMPLOYEES FROM (select distinct DEPTNO1 from EMP1)

2.Using ROW_NUMBER() and SYS_CONNECT_BY_PATH functions in Oracle 9i
This is a new concept which is available oracle 9i and later.

SELECT DEPTNO1,
LTRIM(MAX(SYS_CONNECT_BY_PATH(Ename,','))
KEEP (DENSE_RANK LAST ORDER BY curr), ',') AS EMPLOYEES
FROM
(SELECT DEPTNO1, ENAME,
ROW_NUMBER() OVER (PARTITION BY DEPTNO1 ORDER BY Ename) AS curr,
ROW_NUMBER() OVER (PARTITION BY DEPTNO1 ORDER BY Ename) - 1 AS prev
FROM EMP1
)
GROUP BY DEPTNO1
CONNECT BY prev = PRIOR curr AND DEPTNO1 = PRIOR DEPTNO1
START WITH curr = 1

4. Using XML concept
This concept uses the XML tags and concatenate with tags.

SELECT DEPTNO1,
SUBSTR(
REPLACE(
REPLACE(XMLAgg(XMLElement("dummy",ENAME)),'</dummy>','')
,'<dummy>',',')
,2)
AS EMPLOYEES
FROM EMP1 GROUP BY DEPTNO1

The Output Will be like

DEPTNO1	EMPLOYEES
10	allen ,scot
20	role ,stamp ,timn
30	paln