Thread: Concat field result in select query

<div dir="ltr">Hi all,<br /><br />I was wondering if there is a way to concatenate the results of a field in a select
toreturn it as a single string.<br /><br />Example :<br /><br />my_field<br />--------------<br />1<br />2<br />3<br
/><br/>select concat_something(my_field) from my_table group by something;<br /> the result expected would be someting
likethat : 1/2/3 (with a separator it would be really nice)<br /><br />I hope that I am not asking for too much ;)<br
/><br/>Thanks to all<br /></div> 

array_accum ?

On Fri, 2008-08-22 at 15:40 +0200, Nacef LABIDI wrote:
> Hi all,
> 
> I was wondering if there is a way to concatenate the results of a
> field in a select to return it as a single string.
> 
> Example :
> 
> my_field
> --------------
> 1
> 2
> 3
> 
> select concat_something(my_field) from my_table group by something;
> the result expected would be someting like that : 1/2/3 (with a
> separator it would be really nice)
> 
> I hope that I am not asking for too much ;)
> 
> Thanks to all
> 
-- 
Julien Cigar
Belgian Biodiversity Platform
http://www.biodiversity.be
Université Libre de Bruxelles (ULB)
Campus de la Plaine CP 257
Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
Boulevard du Triomphe, entrée ULB 2
B-1050 Bruxelles
Mail: jcigar@ulb.ac.be
@biobel: http://biobel.biodiversity.be/person/show/471
Tel : 02 650 57 52

Nacef LABIDI a écrit :
> Hi all,
> 
> I was wondering if there is a way to concatenate the results of a field
> in a select to return it as a single string.
> 
> Example :
> 
> my_field
> --------------
> 1
> 2
> 3
> 
> select concat_something(my_field) from my_table group by something;
> the result expected would be someting like that : 1/2/3 (with a
> separator it would be really nice)
> 
> I hope that I am not asking for too much ;)
> 

Something like:
 SELECT array_to_string(array(select myfield from mytable), ','));

For example:

sherkin=# select * from t1;c1 | c2
----+---- 1 | 2 |  3 2 |  4 2 |  5
(4 lignes)

sherkin=# SELECT array_to_string(array(select c1 from t1), ',');array_to_string
-----------------1,2,2,2
(1 ligne)


-- 
Guillaume.http://www.postgresqlfr.orghttp://dalibo.com

Also, the array_accum aggregate isn't present by default, you need to
create it with :

CREATE AGGREGATE array_accum (anyelement)
(   sfunc = array_append,   stype = anyarray,   initcond = '{}'
);

On Fri, 2008-08-22 at 18:24 +0200, Julien Cigar wrote:
> of course: http://rafb.net/p/EOaYfO59.html
> 
> Julien
> 
> On Fri, 2008-08-22 at 15:58 +0200, Nacef LABIDI wrote:
> > any explanations ?
> > 
> > On Fri, Aug 22, 2008 at 6:07 PM, Julien Cigar <jcigar@ulb.ac.be>
> > wrote:
> >         array_accum ?
> >         
> >         
> >         On Fri, 2008-08-22 at 15:40 +0200, Nacef LABIDI wrote:
> >         > Hi all,
> >         >
> >         > I was wondering if there is a way to concatenate the results
> >         of a
> >         > field in a select to return it as a single string.
> >         >
> >         > Example :
> >         >
> >         > my_field
> >         > --------------
> >         > 1
> >         > 2
> >         > 3
> >         >
> >         > select concat_something(my_field) from my_table group by
> >         something;
> >         > the result expected would be someting like that : 1/2/3
> >         (with a
> >         > separator it would be really nice)
> >         >
> >         > I hope that I am not asking for too much ;)
> >         >
> >         > Thanks to all
> >         >
> >         
> >         --
> >         Julien Cigar
> >         Belgian Biodiversity Platform
> >         http://www.biodiversity.be
> >         Université Libre de Bruxelles (ULB)
> >         Campus de la Plaine CP 257
> >         Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
> >         Boulevard du Triomphe, entrée ULB 2
> >         B-1050 Bruxelles
> >         Mail: jcigar@ulb.ac.be
> >         @biobel: http://biobel.biodiversity.be/person/show/471
> >         Tel : 02 650 57 52
> >         
> > 
> > 
-- 
Julien Cigar
Belgian Biodiversity Platform
http://www.biodiversity.be
Université Libre de Bruxelles (ULB)
Campus de la Plaine CP 257
Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
Boulevard du Triomphe, entrée ULB 2
B-1050 Bruxelles
Mail: jcigar@ulb.ac.be
@biobel: http://biobel.biodiversity.be/person/show/471
Tel : 02 650 57 52

of course: http://rafb.net/p/EOaYfO59.html

Julien

On Fri, 2008-08-22 at 15:58 +0200, Nacef LABIDI wrote:
> any explanations ?
> 
> On Fri, Aug 22, 2008 at 6:07 PM, Julien Cigar <jcigar@ulb.ac.be>
> wrote:
>         array_accum ?
>         
>         
>         On Fri, 2008-08-22 at 15:40 +0200, Nacef LABIDI wrote:
>         > Hi all,
>         >
>         > I was wondering if there is a way to concatenate the results
>         of a
>         > field in a select to return it as a single string.
>         >
>         > Example :
>         >
>         > my_field
>         > --------------
>         > 1
>         > 2
>         > 3
>         >
>         > select concat_something(my_field) from my_table group by
>         something;
>         > the result expected would be someting like that : 1/2/3
>         (with a
>         > separator it would be really nice)
>         >
>         > I hope that I am not asking for too much ;)
>         >
>         > Thanks to all
>         >
>         
>         --
>         Julien Cigar
>         Belgian Biodiversity Platform
>         http://www.biodiversity.be
>         Université Libre de Bruxelles (ULB)
>         Campus de la Plaine CP 257
>         Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
>         Boulevard du Triomphe, entrée ULB 2
>         B-1050 Bruxelles
>         Mail: jcigar@ulb.ac.be
>         @biobel: http://biobel.biodiversity.be/person/show/471
>         Tel : 02 650 57 52
>         
> 
> 
-- 
Julien Cigar
Belgian Biodiversity Platform
http://www.biodiversity.be
Université Libre de Bruxelles (ULB)
Campus de la Plaine CP 257
Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
Boulevard du Triomphe, entrée ULB 2
B-1050 Bruxelles
Mail: jcigar@ulb.ac.be
@biobel: http://biobel.biodiversity.be/person/show/471
Tel : 02 650 57 52

> Hi all,
> 
> I was wondering if there is a way to concatenate the
> results of a field in a
> select to return it as a single string.
> 
> Example :
> 
> my_field
> --------------
> 1
> 2
> 3
> 
> select concat_something(my_field) from my_table group by
> something;
> the result expected would be someting like that : 1/2/3
> (with a separator it
> would be really nice)
> 
> I hope that I am not asking for too much ;)
> 
> Thanks to all

you can use aggregate function, check for previous threads for this solution

http://www.postgresql.org/docs/8.3/static/sql-createaggregate.html