DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=f9ioSXZx+g+Zr+F4Kyc26FnBS610+1bKJZ/sKTXKfR/NmhT/iEKc6vVLpKRfOxgXQU+dfUGLeEBAJkVPGpjteaNgZrH5+FxNmYsLDs6mY40JbN2KLHRYOPnAwUDDjRfi869vd2yM8f5GfedQVeVbjSyioYVv8sXdqUyrbOaLBs4=
Message-ID: <162867790712210624y1e3454ffw8d4bffff69819335@mail.gmail.com>
Date: Fri, 21 Dec 2007 15:24:46 +0100
From: "Pavel Stehule" <pavel.stehule@gmail.com>
To: "Philippe Lang" <philippe.lang@attiksystem.ch>
Subject: Re: Concatenation through SQL
Cc: pgsql-sql@postgresql.org
In-Reply-To: <6C0CF58A187DA5479245E0830AF84F4218CFBE@poweredge.attiksystem.ch>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <6C0CF58A187DA5479245E0830AF84F42337F97@poweredge.attiksystem.ch>
	<20071221105813.GA18247@KanotixBox>
	<6C0CF58A187DA5479245E0830AF84F4218CFBE@poweredge.attiksystem.ch>

On 21/12/2007, Philippe Lang <philippe.lang@attiksystem.ch> wrote:
> pgsql-sql-owner@postgresql.org wrote:
> > Philippe Lang <philippe.lang@attiksystem.ch> schrieb:
> >
> > 2 ways:
> >
> > * You can use something like this:
> >
> > test=*# select * from a;
> >  id | val
> > ----+-----
> >   1 | foo
> >   2 | bar
> > (2 rows)
> >
> > test=*# select array_to_string(array(select val from a), ', ');
> > array_to_string -----------------
> >  foo, bar
> > (1 row)
> >
> >
> > * comma-aggregate, see
> > http://www.zigo.dhs.org/postgresql/#comma_aggregate
>
> Hi,
>
> Thanks to all who responded, in particular Andreas. I'm always amazed by
> the quality of both the Postgresql database and the support in its
> newsgroups.
>
> The "comma-aggregate" worked just fine for me. Here is the final
> example, for those willing to test it. This will be more than useful!
>
> ---------------------------------------
> CREATE TABLE appointments
> (
>   id integer,
>   name varchar(32),
>   CONSTRAINT appointments_pkey PRIMARY KEY (id)
> );
>
> CREATE TABLE employees
> (
>   id integer,
>   appointment_id integer,
>   name varchar(32),
>   CONSTRAINT employees_pkey PRIMARY KEY (id),
>   CONSTRAINT appointments_employees_fkey FOREIGN KEY (appointment_id)
> REFERENCES appointments (id)
> );
>
> INSERT INTO appointments VALUES (1, 'app1');
> INSERT INTO employees VALUES (1, 1, 'emp1');
> INSERT INTO employees VALUES (2, 1, 'emp2');
> INSERT INTO employees VALUES (3, 1, 'emp3');
>
> INSERT INTO appointments VALUES (2, 'app2');
> INSERT INTO employees VALUES (4, 2, 'emp1');
> INSERT INTO employees VALUES (5, 2, 'emp4');
>
> CREATE AGGREGATE array_accum (anyelement)
> (
>   sfunc = array_append,
>   stype = anyarray,
>   initcond = '{}'
> );
>
> SELECT
> appointments.name AS appointment,
> (SELECT COUNT(*) FROM employees AS e where e.appointment_id =
> appointments.id) AS num_employees,
> (SELECT array_to_string(array_accum(name),', ') FROM employees AS e
> WHERE e.appointment_id = appointments.id) AS employees
> FROM appointments
> ---------------------------------------
>

hello,

one note: you can merge your two subselect into one. It's unimportant
on 100 rows, but it's can be important on thousands rows.

nice a day
Pavel

>
> Result:
>
> ---------------------------------------------------
> appointment    count_employees    employees
> ---------------------------------------------------
> app1           3                  emp1, emp2, emp3
> app2           2                  emp1, emp4,
> ---------------------------------------------------
>
>
> Merry christmas to all.
>
> Philippe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>