I would like to concatenate sorted strings in an aggregate function. I found a way to do it without sorting[1], but not with.
Here is an example of a setup and what I could like to achieve. Does anyone have suggestions on what is the best way to get the desired result? Thanks, Steven. CREATE TABLE a ( -- Names id INT PRIMARY KEY, name TEXT NOT NULL); CREATE TABLE b ( -- Codes id INT PRIMARY KEY, code CHAR(2) NOT NULL); CREATE TABLE ab ( -- m:n relationship between a and b id SERIAL PRIMARY KEY, a_id INT NOT NULL, b_id INT NOT NULL); COPY a(id,name) FROM STDIN DELIMITER '|'; 1|Alice 2|Bob 3|Charlie \. COPY b(id, code) FROM STDIN DELIMITER '|'; 1|a 2|b 3|c 4|d \. COPY ab(a_id, b_id) FROM STDIN DELIMITER '|'; 2|4 2|1 3|2 3|3 \. -- Custom aggregate function which concatenates strings CREATE AGGREGATE concat ( BASETYPE = text, SFUNC = textcat, STYPE = text, INITCOND = '', ); -- Current query SELECT a.name, TRIM(CONCAT(b.code||' ')) AS codes FROM a LEFT JOIN ab ON (a.id=ab.a_id) LEFT JOIN b ON (ab.b_id=b.id) GROUP BY a.name ORDER BY codes; -- Actual output: -- -- name | codes -- ---------+------- -- Alice | -- Charlie | b c -- Bob | d a -- Desired output: -- -- name | codes -- ---------+-------- -- Alice | -- Bob | a d -- Charlie | b c [1] http://www.postgresql.org/docs/8.0/interactive/sql-createaggregate.html -- w: http://www.cl.cam.ac.uk/users/sjm217/ ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings