From: | "Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Concatenation through SQL |
Date: | 2007-12-21 13:48:55 |
Message-ID: | 6C0CF58A187DA5479245E0830AF84F4218CFBE@poweredge.attiksystem.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
pgsql-sql-owner(at)postgresql(dot)org wrote:
> Philippe Lang <philippe(dot)lang(at)attiksystem(dot)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
---------------------------------------
Result:
---------------------------------------------------
appointment count_employees employees
---------------------------------------------------
app1 3 emp1, emp2, emp3
app2 2 emp1, emp4,
---------------------------------------------------
Merry christmas to all.
Philippe
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2007-12-21 14:24:46 | Re: Concatenation through SQL |
Previous Message | Leif B. Kristensen | 2007-12-21 12:31:14 | Re: Concatenation through SQL |