Languages
[Edit]
EN

PostgreSQL - concatenate multiple rows into one field

3 points
Created by:
Broncono
316

In this article, we would like to show you how to concatenate multiple rows into one field in PostgreSQL.

Quick solution:

SELECT 
    "column1",
    STRING_AGG("column2", ',')
FROM "table_name"
GROUP BY "column1";

 

Practical example

To show how to combine multiple rows into one field, we will use the following table:

PostgreSQL - example data used to concatenate rows into one field
PostgreSQL - example data used to concatenate rows into one field

Note:

At the end of this article you can find database preparation SQL queries.

Example

In this example, we want to display all the colors and a list of people who like each color in one field.

Query:

SELECT 
    "favorite_color",
    STRING_AGG("name", ',') AS "people"
FROM "users"
GROUP BY "favorite_color";

Output:

PostgreSQL - concatenate multiple rows into one field - result
PostgreSQL - concatenate multiple rows into one field - results

Database preparation

create_tables.sql file:

CREATE TABLE "users" (
	"id" SERIAL,
	"name" VARCHAR(50) NOT NULL,
	"favorite_color" VARCHAR(100) NOT NULL,
	PRIMARY KEY ("id")
);

insert_data.sql file:

INSERT INTO "users"
	( "name", "favorite_color")
VALUES
	('Tom', 'red'),
	('Chris', 'green'),
	('Kate', 'blue'),
	('Jack', 'green'),
	('Mark', 'green'),
	('Ann', 'orange'),
	('Natalie', 'pink');

PostgreSQL - problems

PostgreSQL - concatenate multiple rows into one field
Native Advertising
🚀
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

❤️💻 🙂

Join