Content-Class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Re: Concatenation through SQL
Date: Fri, 21 Dec 2007 17:01:28 +0100
Message-ID: <6C0CF58A187DA5479245E0830AF84F4218CFC1@poweredge.attiksystem.ch>
Thread-Topic: [SQL] Concatenation through SQL
thread-index: AchD3UMhc5glFieTR++MEVKvLR8a2gADGvig
References: <6C0CF58A187DA5479245E0830AF84F42337F97@poweredge.attiksystem.ch>
	<20071221105813.GA18247@KanotixBox>
	<6C0CF58A187DA5479245E0830AF84F4218CFBE@poweredge.attiksystem.ch>
	<162867790712210624y1e3454ffw8d4bffff69819335@mail.gmail.com>
From: "Philippe Lang" <philippe.lang@attiksystem.ch>
To: <pgsql-sql@postgresql.org>

Pavel Stehule wrote:
=20
>> SELECT
>> appointments.name AS appointment,
>> (SELECT COUNT(*) FROM employees AS e where e.appointment_id =3D
>> appointments.id) AS num_employees,
>> (SELECT array_to_string(array_accum(name),', ') FROM employees AS e
>> WHERE e.appointment_id =3D appointments.id) AS employees FROM
>> appointments ---------------------------------------
>>=20
>=20
> hello,
>=20
> 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.=20
>=20
> nice a day
> Pavel

Hi Pavel,

Since subselects must return one single column, is that what you are
talking about? I have just modified the code you have posted before.

---------------------------
SELECT=20
  name AS appointment,=20
  num_employees,=20
  employees=20

FROM=20
(
  SELECT=20
    appointment_id,=20
    COUNT(*) AS num_employees,=20
    (SELECT array_to_string(array_accum(name),', ') FROM employees WHERE
e.appointment_id =3D appointment_id) AS employees
  FROM employees e=20
  GROUP BY appointment_id
)s

JOIN appointments a on a.id =3D s.appointment_id;
---------------------------

Philippe