Re: Concatenation through SQL
От | Pavel Stehule |
---|---|
Тема | Re: Concatenation through SQL |
Дата | |
Msg-id | 162867790712210307s2e0bf4c3y1f2911c238375f27@mail.gmail.com обсуждение исходный текст |
Ответ на | Concatenation through SQL ("Philippe Lang" <philippe.lang@attiksystem.ch>) |
Список | pgsql-sql |
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,emp4app1 | 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 >
В списке pgsql-sql по дате отправления: