0

I have some locality tables in a PostgreSQL database:

Table loc -- the places themselves
---------
| id |  name
|  1 | Park X
|  2 | City A
|  3 | City B

Table locdad -- the hierarchical relationship between places
------------
| id | dad | loc
|  1 |  2  |  1
|  1 |  3  |  1

This describes a National Park "X" that covers city A and city B, i.e. the park has two "fathers" in the hierarchical scheme.

When I do a joined query, I get two lines for this park:

select l.id,l.name loc,l1.name dad
from loc l
join locdad ld on ld.loc = l.id
join loc l1 on l1.id = ld.dad
where l.id=1

| id |   loc  |   dad
|  1 | Park X | City A
|  1 | Park X | City B

I would like to combine the result into:

| id |   loc  |      dad
|  1 | Park X | City A, City B

How can I do that?

Rodrigo
  • 4,706
  • 6
  • 51
  • 94

1 Answers1

3

Try using array_agg function like this:

select l.id,l.name loc,array_agg(l1.name) dad
from loc l
join locdad ld on ld.loc = l.id
join loc l1 on l1.id = ld.dad
where l.id=1
GROUP BY l.id,l.name

It depends on your postgres version, but to get strings seperated by commas - for 9.0+ use:

select l.id,l.name loc,string_agg(l1.name, ', ') dad
from loc l
join locdad ld on ld.loc = l.id
join loc l1 on l1.id = ld.dad
where l.id=1
GROUP BY l.id,l.name

Or this for 8.4

select l.id,l.name loc,array_to_string(array_agg(l1.name), ', ') dad
from loc l
join locdad ld on ld.loc = l.id
join loc l1 on l1.id = ld.dad
where l.id=1
GROUP BY l.id,l.name
  • Thank you, Yossi, it works! But it gives the result as '{"City A","City B"}'. Do you know if it's possible to get 'City A, City B', without the { } and " ", and with a space after the comma? (I can do it in php or javascript, but it would be probably faster in sql.) – Rodrigo Feb 29 '16 at 14:46
  • @Rodrigo I've updated the question.. it depends on your version –  Feb 29 '16 at 14:49
  • Perfect! Thanks a lot! – Rodrigo Feb 29 '16 at 14:52