sql: aggregate functions & string join / concatenation [duplicate]

Question

Possible Duplicate:
How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?

(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?

Michael Buen · Accepted Answer · 2010-05-17 09:40:11Z

14

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

answered May 17, 2010 at 9:40

Michael Buen

39.6k10 gold badges99 silver badges120 bronze badges

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

2 Comments

EoghanM Over a year ago

hmm don't seem to have those functions defined - can you help me close the question as it's a duplicate of 43870 ?

Michael Buen Over a year ago

try to upgrade to 8.4, array_agg is built-in. regarding closing the question, it needs 5 close, as of now only 1 closed your question. you can delete your own question

Bob Folkerts · Accepted Answer · 2012-07-06 06:18:50Z

There is a string_agg query in PostreSQL 9. I have a table of regions and a table of departments, where there are multiple departments in a region (e.g. France). My sample query is:

select r.name, string_agg(d.name, ',') 
from regions r
join departments d on d.region = r.code
group by r.name
order by r.name;

This gives me rows like

Picardie Aisne,Oise,Somme

Things get a bit messy if you wan to change the order of the aggregated string. This works, but I have a pathological dislike of any query with distinct:

select distinct r.name as region, string_agg(d.name, ',') over w as departments
from regions r
join departments d on d.region = r.code
window w as (partition by r.name order by d.name desc 
    rows between unbounded preceding and unbounded following)

Collectives™ on Stack Overflow

sql: aggregate functions & string join / concatenation [duplicate]

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related