Combining several rows

[Matthias Nagl]

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Mike Mascari]

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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Holger Klawitter]

-----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-----
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

Matthias Nagl <[e-mail]> 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...3/msg00381.php

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Greg Stark]

Matthias Nagl <[e-mail]> 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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Matthias Nagl]

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

iD8DBQFAsnDLfg4gS83RRywRAmzWAJ9gzOVDbKsyjxB6tu08lD p6A+bT9wCfZ5QG
HOB3GYN85ldJJcvdH6W5F7I=
=Yw/g
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster