Thread: Combining several rows
Hello List! I would like to combine the contents of several rows of a subquery. After several hours of search in the documentation and the internet I didn'T find a solution and hope anyone can help. My problem: Let's say I've got a table in the following form: SELECT * FROM test; id | name ----------- 1 | abc 2 | def 3 | ghi For a table like this I am looking for a query that returns a result that looks this way: name ------------- abc, def, ghi It should work for any number of rows. I would like to Insert the returned String (with a comma separated list of all name-fields in the test-table) in the main-query ( SELECT (whatever is a solution) AS name, other, fields FROM tables...). Thanks in advance Matthias Nagl
Matthias Nagl wrote: > Hello List! > > I would like to combine the contents of several rows of a subquery. After > several hours of search in the documentation and the internet I didn'T find a > solution and hope anyone can help. My problem: > > Let's say I've got a table in the following form: > > SELECT * FROM test; > > id | name > ----------- > 1 | abc > 2 | def > 3 | ghi > > For a table like this I am looking for a query that returns a result that > looks this way: > > name > ------------- > abc, def, ghi Joe Conway's crosstab() function in the 'tablefunc' contrib directory of the source tree is probably what you want. If you've installed by RPM, the postgresql-contrib package will have installed the SQL script to initialize the function in /usr/share/pgsql/contrib/tablefunc.sql. HTH, Mike Mascari
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, > I would like to combine the contents of several rows of a subquery. After > several hours of search in the documentation and the internet I didn'T find > a solution and hope anyone can help. My problem: You have to create your own aggreate. You can use this one: - --------------------------------------------------- CREATE OR REPLACE FUNCTION join_sep ( text, text, text ) RETURNS text LANGUAGE 'sql' IMMUTABLE AS ' SELECT CASE WHEN $1 IS NULL THEN $3 ELSE $1 || $2 || $3 END; '; - --------------------------------------------------- CREATE OR REPLACE FUNCTION join_text ( text, text ) RETURNS text LANGUAGE 'sql' AS ' SELECT join_sep($1,'', '',$2); '; - --------------------------------------------------- CREATE AGGREGATE join ( BASETYPE = text, SFUNC = join_text, STYPE = text ); Mit freundlichem Gruß / With kind regards Holger Klawitter - -- lists <at> klawitter <dot> de -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2 (GNU/Linux) iD8DBQFAsftE1Xdt0HKSwgYRAg/TAJ4rgGwjPVSwrudQ51NP8Imrw0OWhwCfUnMH h/WlRt3eeNopOtDHYlslnw4= =xA3Q -----END PGP SIGNATURE-----
Matthias Nagl <pg@mnagl.de> writes: > Let's say I've got a table in the following form: > SELECT * FROM test; > id | name > ----------- > 1 | abc > 2 | def > 3 | ghi > For a table like this I am looking for a query that returns a result that > looks this way: > name > ------------- > abc, def, ghi The easy way to do this is with a user-defined aggregate. I believe there are several threads in the mailing list archives that give solutions to exactly this problem. I tried "create aggregate comma" at http://www.pgsql.ru/db/pgsearch/ and got this as the first hit: http://archives.postgresql.org/pgsql-sql/2003-03/msg00381.php regards, tom lane
Matthias Nagl <pg@mnagl.de> writes: > For a table like this I am looking for a query that returns a result that > looks this way: > > name > ------------- > abc, def, ghi You need something like this: create function concat_agg_accum(varchar, varchar) returns varchar as 'select $1 || '', '' || $2' language sql strict immutable; create aggregate concat_agg ( basetype = varchar, stype = varchar, sfunc = concat_agg_accum ); select concat_agg(name) as name, ... -- greg
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Thanks a lot Holger your aggregate function is the perfect solution to my problem. yours Matthias Nagl Am Monday 24 May 2004 15:40 schrieb Holger Klawitter: > Hi, > > > I would like to combine the contents of several rows of a subquery. After > > several hours of search in the documentation and the internet I didn'T > > find a solution and hope anyone can help. My problem: > > You have to create your own aggreate. > You can use this one: > > --------------------------------------------------- > CREATE OR REPLACE FUNCTION > join_sep ( text, text, text ) > RETURNS text > LANGUAGE 'sql' > IMMUTABLE > AS ' > SELECT CASE > WHEN $1 IS NULL THEN $3 > ELSE $1 || $2 || $3 > END; > '; > --------------------------------------------------- > CREATE OR REPLACE FUNCTION > join_text ( text, text ) > RETURNS text > LANGUAGE 'sql' > AS ' > SELECT join_sep($1,'', '',$2); > '; > --------------------------------------------------- > CREATE AGGREGATE join ( > BASETYPE = text, > SFUNC = join_text, > STYPE = text > ); > > Mit freundlichem Gruß / With kind regards > Holger Klawitter -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFAsnDLfg4gS83RRywRAmzWAJ9gzOVDbKsyjxB6tu08lDp6A+bT9wCfZ5QG HOB3GYN85ldJJcvdH6W5F7I= =Yw/g -----END PGP SIGNATURE-----