Oracle - Use of COLLECT Function

Collect function enables us to aggregate data into a collection, retaining multiple records of data within a single row (like a nested table). One of the main benefits of this function is that it makes string aggregation" (one of the web's most-requested Oracle technique) very simple.

EMP TABLE

DEPT TABLE

 

SELECT TEMPDEPT.DEPTNAME AS DEPTNAME,
SELECT TEMPDEPT.DEPTNAME AS DEPTNAME,
COALESCE(SUM(TEMPEMP.SALARY),0) AS TOTALEXP,
COLLECT(TEMPEMP.NAME) AS EMPNAME
FROM TEMPDEPT
LEFT JOIN TEMPEMP ON TEMPEMP.DEPTID = TEMPDEPT.DEPTID
GROUP BY TEMPDEPT.DEPTNAME
ORDER BY TOTALEXP DESC


OUTPUT :-