Aim: A query to show which employees works for which department

Option 1: Using String_Agg function

Purpose: This values helps to concatenate the input values into a string, separated by delimiter. We can pass any delimiter in this function

Syntax: String_Agg(String_Values, delimiter)

Select DeptName, String_Agg((EmpName), ',') "Employee List"
From tblEmployee
Join tblDept
Using(DeptId) 
Group By DeptName;

/* Result */
Deptname	Employee List
--------	-------------
"Accounts"	"Shashi Bhushan,Deepak Singh,Abhijeet Moshambique,Manish Bharat,Shashi Dayal,Amitav Salonki"
"Finance"	"Fatima  Sarani,Sumanta Manik,Sarapati Babulal Apte,Shinarayan Pande"
"IT"		"Deepak Singh,Amitav Mallik,Deepak Kumar Goyal,Amitav Salonki,Shashi Bhushan"
"Sales"		"Amit Ojha"

Option 2: Using Built In Array function

PurposeAll the input values are concatenated into an array

SELECT DeptName, Array_To_String(Array_Agg(EmpName),',') "Employee List"
FROM tblEmployee 
Join tblDept
Using(DeptId) 
Group By DeptName;

Option 3: Using our own string aggregation function

PostgreSQL allows us to create our own Aggregate function as we will look under

Let us first create the 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;

We can even create our function as under

CREATE AGGREGATE ListAggregation2(
      basetype    = Text,
      sfunc       = TextCat,
      stype       = Text,
      initcond    = ''
  );

And use it as under

Select DeptName,Substring(ListAggregation2(',' || EmpName),2) "Employee List" 
FROM tblEmployee 
Join tblDept
Using(DeptId) 
Group By DeptName;

N.B.~ Inorder to Drop an Aggregate function, issue the command DROP AGGREGATE ListAggregation(anyelement)

In Oracle we can use

1. wm_concat function
2. ListAgg function
3. Collect function
4. Or our own function for doing so

In Sql Server, we can use For XML Path for simulating the same

Hope this helps