Let us learn the String aggregation in PostgreSQL

Niladri.Biswas
Posted by in PostgreSQL category on for Beginner level | Points: 250 | Views : 2997 red flag

In this article we will learn various technique for performing 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.

Page copy protected against web site content infringement by Copyscape

About the Author

Niladri.Biswas
Full Name: Niladri Biswas
Member Level: Platinum
Member Status: Member
Member Since: 10/25/2010 11:04:24 AM
Country: India
Best Regards, Niladri Biswas
http://www.dotnetfunda.com
Technical Lead at HCL Technologies

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)