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