Combining several rows

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