Let us consider that we have two tables say Employee and Department with the below data.
/* Employee Table(tblEmployee) */
DeptId EmpName
------- -------
1 Shashi Bhushan
1 Deepak Singh
1 Abhijeet Moshambique
1 Manish Bharat
2 Fatima Sarani
2 Sumanta Manik
3 Amitav Mallik
3 Deepak Kumar Goyal
3 Amitav Salonki
4 Amit Ojha
/* Department Table(tblDept) */
DeptId DeptName
------ --------
1 Accounts
2 Finance
3 IT
4 Sales
*/
And we are looking for the below output
/* Result */
Deptname Employee List
-------- -------------
Accounts Shashi Bhushan,Deepak Singh,Abhijeet Moshambique,Manish Bharat
Finance Fatima Sarani,Sumanta Manik
IT Deepak Kumar Goyal,Amitav Salonki
Sales Amit Ojha
We can achieve this by using custom aggregate function as under
CREATE AGGREGATE ListAggregation1 (anyelement)(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);
Then invoke it as under
Select DeptName,Array_To_String(ListAggregation1(EmpName),',') "Employee List"
FROM tblEmployee
Join tblDept
Using(DeptId)
Group By DeptName;