Re: [SQL] Concatenation through SQL
Philippe Lang <[EMAIL PROTECTED]> 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 Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Concatenation through SQL
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: --- appointmentcount_employeesemployees --- 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
Re: [SQL] Concatenation through SQL
On Friday 21. December 2007, Philippe Lang wrote: >(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. I've got a similar problem. My persons table has a number of fields for different name parts: given, patronym, toponym, surname, occupation, epithet. I've written a Plpgsql function that concatenates the full name, but it's big and ugly. I'd like something more elegant, like the Python or PHP join() function. I tried Andreas' suggestion like this: pgslekt=> select array_to_string(array(select given, patronym, toponym from persons where person_id=57), ' '); ERROR: subquery must return only one column Is there any way to accomplish this from Plpgsql? -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE My Jazz Jukebox: http://www.last.fm/user/leifbk/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Concatenation through 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,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 <[EMAIL PROTECTED]> 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: > > --- > appointmentcount_employeesemployees > --- > 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 > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Concatenation through SQL
On Dec 21, 2007 4:16 PM, Leif B. Kristensen <[EMAIL PROTECTED]> wrote: > On Friday 21. December 2007, Philippe Lang wrote: > > >(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. > > I've got a similar problem. My persons table has a number of fields for > different name parts: given, patronym, toponym, surname, occupation, > epithet. I've written a Plpgsql function that concatenates the full > name, but it's big and ugly. I'd like something more elegant, like the > Python or PHP join() function. I tried Andreas' suggestion like this: > > pgslekt=> select array_to_string(array(select given, patronym, toponym > from persons where person_id=57), ' '); > ERROR: subquery must return only one column Try concatenating your columns using the '||' operator or 'concat' function and project only one column from the subquery. You won't need to convert it to an array and back to string. --Imad Database Expert ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Concatenation through SQL
[EMAIL PROTECTED] wrote:
> Philippe Lang <[EMAIL PROTECTED]> 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:
---
appointmentcount_employeesemployees
---
app1 3 emp1, emp2, emp3
app2 2 emp1, emp4,
---
Merry christmas to all.
Philippe
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Concatenation through SQL
On 21/12/2007, Philippe Lang <[EMAIL PROTECTED]> wrote:
> [EMAIL PROTECTED] wrote:
> > Philippe Lang <[EMAIL PROTECTED]> 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
> ---
>
hello,
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.
nice a day
Pavel
>
> Result:
>
> ---
> appointmentcount_employeesemployees
> ---
> app1 3 emp1, emp2, emp3
> app2 2 emp1, emp4,
> ---
>
>
> Merry christmas to all.
>
> Philippe
>
>
> ---(end of broadcast)---
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>choose an index scan if your joining column's datatypes do not
>match
>
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] Concatenation through SQL
On Friday 21. December 2007, Niklas Johansson wrote: >select array_to_string(array[given, patronym, toponym], ' ') from >persons where person_id=57; > >Notice the use of the array[] constructor, instead of the array() >constructor, which must be fed a subquery which returns only one > column. Aah, great! pgslekt=> select array_to_string(array[given, patronym, toponym], ' ') from persons where person_id=57; array_to_string -- Abraham Jonsen Bjørntvet (1 row) Tackar och bockar! -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE My Jazz Jukebox: http://www.last.fm/user/leifbk/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Concatenation through SQL
On 21 dec 2007, at 12.16, Leif B. Kristensen wrote: I've got a similar problem. My persons table has a number of fields for different name parts: given, patronym, toponym, surname, occupation, epithet. I'd like something more elegant, like the Python or PHP join() function. I tried Andreas' suggestion like this: pgslekt=> select array_to_string(array(select given, patronym, toponym from persons where person_id=57), ' '); ERROR: subquery must return only one column Your case is not the same as Philippe's, since you have the values to be concatenated in columns, whereas he had them in rows. However, something like this would probably achieve what you're looking for: select array_to_string(array[given, patronym, toponym], ' ') from persons where person_id=57; Notice the use of the array[] constructor, instead of the array() constructor, which must be fed a subquery which returns only one column. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Concatenation through SQL
Pavel Stehule wrote: >> 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 --- >> > > hello, > > 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. > > 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 name AS appointment, num_employees, employees FROM ( SELECT appointment_id, COUNT(*) AS num_employees, (SELECT array_to_string(array_accum(name),', ') FROM employees WHERE e.appointment_id = appointment_id) AS employees FROM employees e GROUP BY appointment_id )s JOIN appointments a on a.id = s.appointment_id; --- Philippe ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
