eyknwa
Last Updated: February 25, 2016
·
12.88K
· groodt

Postgres GROUP_CONCAT

In MySQL, you can aggregate columns into a single value using GROUP_CONCAT.

You can achieve the same thing in Postgres using string_agg.

CREATE TABLE animal (
    id serial primary key,
    farm_id integer,
    name varchar
);

INSERT INTO animal (farm_id, name) VALUES (1, 'cow'), (1, 'horse');

CREATE TABLE tool (
    id serial primary key,
    farm_id integer,
    name varchar
);

INSERT INTO tool (farm_id, name) VALUES (1, 'hammer'), (1, 'spade'), (1, 'axe');

SELECT a.farm_id, string_agg(DISTINCT a.name, ',') as animals, string_agg(DISTINCT t.name, ',') as tools
FROM
  animal a, tool t
WHERE a.farm_id = t.farm_id
GROUP BY a.farm_id;

Output:

FARM_ID    ANIMALS        TOOLS
1          cow,horse      axe,hammer,spade

You can try it for yourself on sqlfiddle: http://sqlfiddle.com/#!15/139a1/29/0