Обсуждение: 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-----