How to concatenate strings of a string field in a PostgreSQL 'group by' query?

PostgreSQL 9.0 or later:

Modern Postgres (since 2010) has the string_agg(expression, delimiter) function which will do exactly what the asker was looking for:

SELECT company_id, string_agg(employee, ', ')FROM mytableGROUP BY company_id;

Postgres 9 also added the ability to specify an ORDER BY clause in any aggregate expression; otherwise you have to order all your results or deal with an undefined order. So you can now write:

SELECT company_id, string_agg(employee, ', ' ORDER BY employee)FROM mytableGROUP BY company_id;

PostgreSQL 8.4.x:

PostgreSQL 8.4 (in 2009) introduced the aggregate function array_agg(expression) which collects the values in an array. Then array_to_string() can be used to give the desired result:

SELECT company_id, array_to_string(array_agg(employee), ', ')FROM mytableGROUP BY company_id;

PostgreSQL 8.3.x and older:

When this question was originally posed, there was no built-in aggregate function to concatenate strings. The simplest custom implementation (suggested by Vajda Gabo in this mailing list post, among many others) is to use the built-in textcat function (which lies behind the || operator):

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

Here is the CREATE AGGREGATE documentation.

This simply glues all the strings together, with no separator. In order to get a ", " 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 and tested on 8.3.12:

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;

This version will output a comma even if the value in the row is null or empty, so you get output like this:

a, b, c, , e, , g

If you would prefer to remove extra commas to output this:

a, b, c, e, g

Then add an ELSIF check to the function like this:

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

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 mytableGROUP BY company_id;

As from PostgreSQL 9.0 you can use the aggregate function called string_agg. Your new SQL should look something like this:

SELECT company_id, string_agg(employee, ', ')FROM mytableGROUP BY company_id;