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 14:48:55 +0100
Message-ID: <6C0CF58A187DA5479245E0830AF84F4218CFBE@poweredge.attiksystem.ch>
Thread-Topic: [SQL] Concatenation through SQL
thread-index: AchDwIiVIEL+nuqgQDSayMSVSEJdWQAFnQsg
References: <6C0CF58A187DA5479245E0830AF84F42337F97@poweredge.attiksystem.ch>
	<20071221105813.GA18247@KanotixBox>
From: "Philippe Lang" <philippe.lang@attiksystem.ch>
To: <pgsql-sql@postgresql.org>

pgsql-sql-owner@postgresql.org wrote:
> Philippe Lang <philippe.lang@attiksystem.ch> schrieb:
>=20
> 2 ways:
>=20
> * You can use something like this:
>=20
> test=3D*# select * from a;
>  id | val
> ----+-----
>   1 | foo
>   2 | bar
> (2 rows)
>=20
> test=3D*# select array_to_string(array(select val from a), ', ');=20
> array_to_string -----------------
>  foo, bar
> (1 row)
>=20
>=20
> * 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)=20
(=20
  sfunc =3D array_append,=20
  stype =3D anyarray,=20
  initcond =3D '{}'=20
);=20

SELECT=20
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
---------------------------------------


Result:

---------------------------------------------------
appointment    count_employees    employees
---------------------------------------------------
app1           3                  emp1, emp2, emp3
app2           2                  emp1, emp4,
---------------------------------------------------


Merry christmas to all.

Philippe