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=dzF6wzU7MIpCvdaX1uVUpH9+cq1V+Fte/LjCpkg9A37yZ8a/akGU3u289USUlLSGhaCbQmwCFUJAcIAdLBFYxcVEAkNVW0vpVtpL3m0Q565z6mU5AhzE7+DGlz+7B/8e9I7QU2lx6oN5mSud0mHiGN/UTPbNTmrS2H4sNIc5q1w=
Message-ID: <162867790712210307s2e0bf4c3y1f2911c238375f27@mail.gmail.com>
Date: Fri, 21 Dec 2007 12:07:17 +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: <6C0CF58A187DA5479245E0830AF84F42337F97@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>

Hello

postgres=# select name, count, employes from  (select appointment_id,
count(*), (SELECT array_to_string(ARRAY(SELECT name from employees
where appointment_id = e.appointment_id),',')) as employes from
employees e group by appointment_id)s join appointments a on a.id =
s.appointment_id;
 name | count |    employes
------+-------+----------------
 app2 |     2 | emp1,emp4
 app1 |     3 | emp1,emp2,emp3
(2 rows)

regards
Pavel Stehule

for longer table is better define own aggregate function.


On 21/12/2007, Philippe Lang <philippe.lang@attiksystem.ch> wrote:
> Hi,
>
> Here is small reduced test database:
>
> ------------------------------
> 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');
> ------------------------------
>
>
> I'm trying to write an SQL query that would return this:
>
> ---------------------------------------------------
> appointment    count_employees    employees
> ---------------------------------------------------
> app1           3                  emp1, emp2, emp3
> app2           2                  emp1, emp4,
> ---------------------------------------------------
>
> First part is easy to write:
>
> ------------------------------
> SELECT
> appointments.name AS appointment,
> (SELECT COUNT(*) FROM employees AS e where e.appointment_id =
> appointments.id) AS num_employees
> FROM appointments
> ------------------------------
>
> ... But concatenating employees name is harder, at least for me...
>
> I'm convinced this can be done with Set Returning Functions and a bit of
> plpgsql, but I was wondering if it would possible to write some kind of
> extension to Postgresql (operator?) that would allow this kind of
> syntax:
>
> ------------------------------
> SELECT
> appointments.name AS appointment,
> (SELECT COUNT(*) FROM employees AS e where e.appointment_id =
> appointments.id) AS num_employees
> (SELECT CONCAT(name, ', ') FROM employees AS e where e.appointment_id =
> appointments.id) AS employees
> FROM appointments
> ------------------------------
>
> ... where CONCAT suggest we want to concatenate the variable inside,
> with the separator ', ' inbetween.
>
> Thanks for your tips!
>
>
> Philippe Lang
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate
>