What is the purpose of Array_To_String in PostgreSQL?

 Posted by Niladri.Biswas on 2/10/2013 | Category: PostgreSQL Interview questions | Views: 2965 | Points: 40

The "Array_To_String" function concatenates array elements using provided delimiter.

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 Array_To_String function as under

SELECT DeptName, Array_To_String(Array_Agg(EmpName),',') "Employee List"

FROM tblEmployee
Join tblDept
Group By DeptName;

Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response