Merging multiple rows into single row PostgreSQL

Question

I have one main table called deliveries and it has one to many relationship with deliveries_languages as dl, deliveries_markets dm and deliveries_tags dt having delivery_id as foreign key. These 3 tables have one to one relation with languages , markets and tags respectively. Additionaly, deliveries, table have one to one relation with companies and have company_is as foreign key. Following is a query that I have written:

SELECT deliveries.*, languages.display_name, markets.default_name, tags.default_name, companies.name
FROM deliveries
JOIN deliveries_languages dl ON dl.delivery_id = deliveries.id 
JOIN deliveries_markets dm ON dm.delivery_id = deliveries.id 
JOIN deliveries_tags dt ON dt.delivery_id = deliveries.id 
JOIN languages ON languages.id = dl.language_id 
JOIN markets ON markets.id = dm.market_id 
JOIN tags ON tags.id = dt.tag_id 
JOIN companies ON companies.id = deliveries.company_id 
WHERE 
deliveries.name ILIKE '%new%' AND 
deliveries.created_by = '5f331347-fb58-4f63-bcf0-702f132f97c5' AND 
deliveries.deleted_at IS NULL 
LIMIT 10

Here I am getting redundant delivery_ids because for each delivery_id there are multiple languages, markets and tags. I want to use limit on distinct delivery_ids and at the same time, I want those multiple languages, markets and tags to be grouped and populate in single row.

Currently it looks like:

delivery_id | name |languages | markets  | tags
------------|------|----------|----------|-----------
1           | d1   |en        | au       | tag1
1           | d1   |de        | sw       | tag2
2           | d2   |en        | au       | tag1
2           | d2   |de        | sw       | tag2
3           | d3   |en        | au       | tag1
3           | d3   |de        | sw       | tag2

Is tere any way that I can have data look like below:

delivery_id | name |languages | markets  | tags
------------|------|----------|----------|-----------
1           | d1   |en, de    | au,sw    | tag1, tag2
2           | d2   |en, de    | au,sw    | tag1, tag2
3           | d3   |en, de    | au,sw    | tag2, tag3

P.s. above tables contain only part of data, actual query returns many more columns but above are important one here. Can someone please help me to resolve this issue.

Emilio Platzer · Accepted Answer · 2017-09-10 18:45:12Z

14

You can use GROUP BY with string_agg like this:

SELECT deliveries.deliver_id, deliver.name, 
       string_agg(distinct languages.display_name, ',' order by languages.display_name) as langs, 
       string_agg(distinct markets.default_name, ',' order by markets.default_name) as markets,
       string_agg(distinct tags.default_name, ',' order by tags.default_name) as tags,
       string_agg(distinct companies.name, ',' order by companies.name) as companies
    ...
    GROUP BY deliveries.deliver_id, deliver.name;

answered Sep 10, 2017 at 18:45

Emilio Platzer

2,5191 gold badge23 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Merging multiple rows into single row PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related