Let us learn the String aggregation in PostgreSQL

Introduction

Sometimes we need to aggregate data from a number of rows into a single row, giving a list of data associated with a specific value.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

This article will tell us how to do so using PostgreSQL's function.

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;

Using Clause is a new way of performing Inner/Equi join. We can use it when columns shares the same name.The "String_Agg" function,input values concatenated into a string separated by delimiter.

Option 2: Using Built In Array function

Purpose: All 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;

The "Array_Agg" function first concats the input values into an array.The "Array_To_String" then acts upon it and concatenates array elements using provided delimiter.

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)

Conclusion

In this article we have learnt various technique for performing string aggregation in PostgreSQL.Hope this will be helpful.Thanks for reading.