sql: aggregate functions & string join / concatenation

Question

(I'm using postgres)

Are there any aggregate functions that work on strings?

I want to write a query along the lines of

select table1.name, join(' - ', unique(table2.horse)) as all_horses
from table1 inner join table2 on table1.id = table2.fk
group by table1.name

Given these 2 tables:

| table1          |               | table2                    |
| id (pk) | name  |               | id (pk) | horse   |  fk   |
+---------+-------+               +---------+---------+-------+ 
|       1 | john  |               |       1 | redrum  |     1 |
|       2 | frank |               |       2 | chaser  |     1 |
                                  |       3 | cigar   |     2 |

The query should return:

| name   |   all_horses      |
+--------+-------------------+
| john   |   redrum - chaser |
| frank  |   cigar           |

Do functions that along the lines of join and unique exist in any DBs for strings?

Answer 1

select table1.name, 
    array_to_string( array_agg( distinct table2.horse ), ' - ' ) as all_horses
from table1 inner join table2 on table1.id = table2.fk
group by table1.name