views:

13042

answers:

9

I am going to answer my own question because I just found the answer, but thought it still worth posting here.

I am looking for a way to concatenate the strings of a field within a group by query. So for example, I have a table:

ID   COMPANY_ID   EMPLOYEE
1    1            Anna
2    1            Bill
3    2            Carol
4    2            Dave

and I wanted to group by company_id to get something like:

COMPANY_ID   EMPLOYEE
1            Anna, Bill
2            Carol, Dave

There is an in-built function in mySQL to do this group_concat

+5  A: 

I claim no credit for the answer because I found it after some searching:

What I didn't know is that PostgreSQL allows you to define your own aggregate functions with CREATE AGGREGATE

This post on the PostgreSQL list shows how trivial it is to create a function to do what's required:

CREATE AGGREGATE textcat_all(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);

SELECT company_id, textcat_all(employee || ', ')
FROM mytable
GROUP BY company_id;
Guy C
+9  A: 

Update as of PostgreSQL 9.0:

The new version of Postgres will have the string_agg() function that will do exactly what the question asked for, even letting you speficy the delimiter string: PostgreSQL 9 General-Purpose Aggregate Functions

Original Answer (for pre-9.0 Postgres)

I've run into this before also. There is no built-in aggregate function to concatenate strings. It seems like this would be needed all the time, but it's just not part of the default set.

I Googled and found the same example:

CREATE AGGREGATE textcat_all(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);

Here is the CREATE AGGREGATE documentation.

In order to get the ", " inserted in between them without having it at the end, you might want to make your own concatenation function and substitute it for the "textcat" above. Here is one I put together but haven't tested:

CREATE FUNCTION commacat(acc text, instr text) RETURNS text AS $$
  BEGIN
    IF acc IS NULL OR acc = '' THEN
      RETURN instr;
    ELSE
      RETURN acc || ', ' || instr;
    END IF;
  END;
$$ LANGUAGE plpgsql;

(edit - changed "varchar" in my function to "text" as pointed out by Kev)

Neall
Kev
You can write the function in SQL only, which is easier for installation (plpgsql has to be installed by the superuser). See my post for an example.
bortzmeyer
+1  A: 

As already mentioned, creating your own aggregate function is the right thing to do. Here is my concatenation aggregate function (you can find details in French):

CREATE OR REPLACE FUNCTION concat2(text, text) RETURNS text AS '
    SELECT CASE WHEN $1 IS NULL OR $1 = \'\' THEN $2
            WHEN $2 IS NULL OR $2 = \'\' THEN $1
            ELSE $1 || \' / \' || $2
            END; 
'
 LANGUAGE SQL;

CREATE AGGREGATE concatenate (
  sfunc = concat2,
  basetype = text,
  stype = text,
  initcond = ''

);

And then use it as:

SELECT company_id, concatenate(employee) AS employees FROM ...
bortzmeyer
+2  A: 

This latest announcement list snippet might be of interest if you'll be upgrading to 8.4:

Until 8.4 comes out with a super-effient native one, you can add the array_accum() function in the PostgreSQL documentation for rolling up any column into an array, which can then be used by application code, or combined with array_to_string() to format it as a list:

http://www.postgresql.org/docs/current/static/xaggr.html

I'd link to the 8.4 development docs but they don't seem to list this feature yet.

Kev
A: 

I found this PostgreSQL documentation helpful: http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html.

In my case, I sought plain SQL to concatenate a field with brackets around it, if the field is not empty.

select itemid, 
  CASE 
    itemdescription WHEN '' THEN itemname 
    ELSE itemname || ' (' || itemdescription || ')' 
  END 
from items;
+1  A: 

following up on Kev's answer, using Postgres docu:

First, create an array of the elements, then use the built-in array_to_string function.

CREATE AGGREGATE array_accum (anyelement)
(
 sfunc = array_append,
 stype = anyarray,
 initcond = '{}'
);

select array_to_string(array_accum(name),'|') from table group by id;
A: 

Following yet again on the use of a custom aggregate function of string concatenation: you need to remember that the select statement will place rows in any order, so you will need to do a sub select in the from statement with an order by clause, and then an outer select with a group by clause to aggregate the strings thus

SELECT custom_aggregate(MY.special_strings) FROM (SELECT special_strings, grouping_column FROM a_table ORDER BY ordering_column) MY GROUP BY MY.grouping_column

+4  A: 

How about using Postgres built-in array functions? At least on 8.4 this works out of the box:

SELECT company_id, array_to_string(array_agg(employee), ',')
FROM mytable
GROUP BY company_id;
Markus Döring
this works great, thanks!
CNelson
A: 

Thank you Florian, it works nicely in 8.2. Döring, your version is usable in 8.4.

Visitor