EN
PostgreSQL - concatenate multiple rows into one field
3
points
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:
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:
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');