Search This Blog

Sunday, October 20, 2013

GREENPLUM: String concatenation for multiple rows in same table

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

Update as of PostgreSQL 9.0:
Newer versions of PostgreSQL have the string_agg(expression, delimiter) function that will do exactly what the question asked for, even letting you specify the delimiter string
Update as of PostgreSQL 8.4:
PostgreSQL 8.4 introduced the aggregate function array_agg(expression) which concatenates the values into an array. Then array_to_string() can be used to give the desired result:
SELECT company_id, array_to_string(array_agg(employee), ',')
FROM mytable
GROUP BY company_id;
There is no built-in aggregate function to concatenate strings. It seems like this would be needed, but it's not part of the default set. A web search however reveals some manual implementations:
CREATE AGGREGATE textcat_all(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);
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.:
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;
Note: The function above will output a comma even if the value in the row is null/empty, which outputs:
a, b, c, , e, , g
If you would prefer to remove extra commas to output:
a, b, c, e, g
just add an ELSIF check to the function:
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;

No comments:

Post a Comment