How to concatenate the string values of a column/field in a PostgreSQL query?

This is a very popular interview question of the database, many times I faced this the question in SQL Server interview.

Problem: Suppose we have the following table (employee)

id	deprt_id	emp_name
1	1	Dilip
2	1	Anil
3	1	Mamta
4	2	Ashish
5	2	Naina

And I want to result set as below

deprt_id	emp_name
1	Dilip,Anil,Mamta
2	Ashish,Naina,Kartikey

To achieve this above result-set we will work on the PostgreSQL query. As new versions of the database come along, some new features and enhancements are also added. According to the different versions, whatever features are available here, I will give detailed information about it.

Create table employee

CREATE TABLE employee (        id     int GENERATED BY DEFAULT AS IDENTITY,        deprt_id text,        emp_name text ); INSERT INTO employee(deprt_id,emp_name) VALUES (1,'Dilip'); INSERT INTO employee (deprt_id,emp_name) VALUES (1,'Anil'); INSERT INTO employee (deprt_id,emp_name) VALUES (1,'Mamta'); INSERT INTO employee (deprt_id,emp_name) VALUES (2,'Ashish'); INSERT INTO employee (deprt_id,emp_name) VALUES (2,'Naina'); INSERT INTO employee (deprt_id,emp_name) VALUES (2,'Kartikey');

PostgreSQL 9.0 or later

The function string_agg(expression, delimiter) is used to concatenate string value of a column, run the following query.

SELECT deprt_id, string_agg(emp_name, ',') FROM employee   GROUP BY deprt_id;

ORDER BY clause in any aggregate expression string_agg(column_name, ',', order by column_name).

SELECT deprt_id, string_agg(emp_name, ',' ORDER BY employee) FROM employee GROUP BY deprt_id;

PostgreSQL 8.4 or later

The function array_to_string() can be used to give the desired result.

SELECT deprt_id, array_to_string(array_agg(emp_name), ',') FROM employee GROUP BY deprt_id;