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;






No comments:

Post a Comment

Please do not enter any spam link in the comment box.

NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...