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:
xxxxxxxxxx1
SELECT 2
"column1",3
STRING_AGG("column2", ',')4
FROM "table_name"5
GROUP BY "column1";
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.
In this example, we want to display all the colors and a list of people who like each color in one field.
Query:
xxxxxxxxxx1
SELECT 2
"favorite_color",3
STRING_AGG("name", ',') AS "people"4
FROM "users"5
GROUP BY "favorite_color";Output:
create_tables.sql file:
xxxxxxxxxx1
CREATE TABLE "users" (2
"id" SERIAL,3
"name" VARCHAR(50) NOT NULL,4
"favorite_color" VARCHAR(100) NOT NULL,5
PRIMARY KEY ("id")6
);insert_data.sql file:
xxxxxxxxxx1
INSERT INTO "users"2
( "name", "favorite_color")3
VALUES4
('Tom', 'red'),5
('Chris', 'green'),6
('Kate', 'blue'),7
('Jack', 'green'),8
('Mark', 'green'),9
('Ann', 'orange'),10
('Natalie', 'pink');