psql 9.1 merge select result in one row

Question

I have 4 tables and each have over 10K row:

                         Table "public.items"
 Column         |           Type   |   Modifiers                      
----------------+------------------+---------------
 id             | integer          | not null default nextval       
 title          | text             | not null   

           Table "public.issues"
Column       |   Type          | Modifiers                                
-------------+-----------------+------------------
id           | integer          | not null default nextval
name         | text             | not null  

 Table "public.authors"
  Column     |        Type          |     Modifiers                       
--------------+---------------------+-----------------------
id           | integer              | not null default  nextval
first_name   | text                 | not null
last_name    | text                 | not null

Table "public.item_issues"
  Column  |  Type   | Modifiers 
----------+---------+-----------
 item_id  | integer | not null
 issue_id | integer | not null


Table "public.item_authors"
 Column   |  Type   | Modifiers 
 -----------+---------+-----------
item_id   | integer | not null
author_id | integer | not null

I want to list all the items with their author(s) and issues knowing that an item can have more than one issue and more than one author. So far my select query is returning the right infos but in several row.

The query:

select i.id, i.title, iss.id  as issue_id, iss.name, a.first_name, a.last_name
join item_authors ia on (ia.item_id = i.id) 
join authors a on (a.id = ia.author_id) 
join issues iss on (iss.id = iis.issue_id)
join item_issues iis on (iis.item_id = i.id)

The result:

id  | title | issue_id | name    | first_name | last_name       
----|-------|----------|---------|------------|--------------   
 1  |  A    |  22      | elected | David      | Lowes  
 1  |  A    |  54      | health  | David      | Lowes 
 2  |  C    |  10      | art     | Adam       | Dole 
 2  |  C    |  10      | art     | John       | Paul 
 3  |  D    |  28      | eat     | Barbara    | Wu  
 3  |  D    |  17      | weight  | Barbara    | Wu  
 3  |  D    |  54      | health  | Barbara    | Wu  
 3  |  D    |  28      | eat     | Sam        | Sara 
 3  |  D    |  17      | weight  | Sam        | Sara 
 3  |  D    |  54      | health  | Adam       | Dole  
 3  |  D    |  28      | eat     | Adam       | Dole  
 3  |  D    |  17      | weight  | Adam       | Dole 
 3  |  D    |  54      | health  | Sam        | Sara

but i want :

id  | title | issue_id   |       name          | first_name last_name 
----|-------|------------|---------------------|--------------------------
 1  |  A    | 22,54      | elected, health     | David Lowes 
 2  |  C    | 10         | art                 | John Paul, Adam Dole 
 3  |  D    | 28, 17, 54 | eat, weight, health | Barbara Wu, Sam Sara, Adam Dole

is it possible in one query?

Iurii Tkachenko · Accepted Answer · 2015-12-10T15:50:42.523

0

Using array_agg and array_to_string functions combination you can do something like this.

select i.id, i.title, 
  array_to_string(array_agg(iss.id), ', ') as issue_id, 
  array_to_string(array_agg(iss.name), ', ') as name, 
  array_to_string(array_agg(a.first_name || a.last_name), ', ') as "first_name last_name"
from issues i
join item_authors ia on ia.item_id = i.id
join authors a on a.id = ia.author_id
join issues iss on iss.id = iis.issue_id
join item_issues iis on iis.item_id = i.id
group by i.id, i.title

edited Dec 10 '15 at 15:50

answered Dec 09 '15 at 22:29

Iurii Tkachenko

3,106
29
34

It is giving me an error: ERROR: function array_agg(text, unknown) does not exist – mamesaye Dec 10 '15 at 15:45
@mamesaye I think it was a problem with brackets. Here is a simple sqlfiddle to help you http://sqlfiddle.com/#!15/23c84/1 – Iurii Tkachenko Dec 10 '15 at 15:56
Thanks it is working. How can i remove the doubles (D,D): http://sqlfiddle.com/#!15/5c9a4/1 – mamesaye Dec 10 '15 at 16:27
nvm, got it. adding distinct before the column name did the trick. – mamesaye Dec 10 '15 at 17:07

psql 9.1 merge select result in one row

1 Answers1