Обсуждение: How to get a result in one row
Hi! I'm using PostgreSQL 7.4.7. table_cid | nick ----+------ 1 | T 2 | S 3 | G 4 | A 5 | D ... table_m id | c ------+---- 22192 | 4 15041 | 3 21764 | 5 22192 | 1 15041 | 4 15041 | 2 ... where table_m.c is a foreign key on table_c.id SELECT table_m.id,table_c.nickFROM table_m AS m JOIN table_c AS c ON c.id=m.c WHERE m.id=22192 ORDER BY c.nick; returns: id | nick ------+------ 22192 | A 22192 | T (2 rows) I'd like to get the result in only one row: id | nick ------+------ 22192 | A,T (and similarly: 15041 | A,G,S ) As table_c can increase, I don't want to use the case construct. How can I do? Maybe writing a function. But how? TIA! virgi
At 11:06 AM 6/21/06, virgi@lettere.unipd.it wrote: >returns: > id | nick >------+------ >22192 | A >22192 | T >(2 rows) > >I'd like to get the result in only one row: > id | nick >------+------ >22192 | A,T This question is in the archives (probably more than once). The answer is... Read the online docs about aggregate functions. There is an example that does (almost) exactly what you are asking.
> >I'd like to get the result in only one row: > > id | nick > >------+------ > >22192 | A,T > This question is in the archives (probably more than once). The answer is...> > Read the online docs about aggregate functions. There is an example that > does (almost) exactly what you are asking. Where you referring to the tread regarding the LTREE contrib module for postgresql? http://archives.postgresql.org/pgsql-general/2006-06/msg00745.php I know I've seen this done using cursors in PL-PGSQL, but I would be interested if there was a solution with pre-existing aggregates. Regards, Richard Broersma Jr.
At 02:24 PM 6/21/06, Richard Broersma Jr wrote: > > >I'd like to get the result in only one row: > > > id | nick > > >------+------ > > >22192 | A,T > > This question is in the archives (probably more than once). The answer > is...> > > Read the online docs about aggregate functions. There is an example that > > does (almost) exactly what you are asking. > >Where you referring to the tread regarding the LTREE contrib module for >postgresql? >http://archives.postgresql.org/pgsql-general/2006-06/msg00745.php > >I know I've seen this done using cursors in PL-PGSQL, but I would be >interested if there was a >solution with pre-existing aggregates. I was referring to threads like: http://archives.postgresql.org/pgsql-sql/2004-10/msg00124.php and threads on 9.Feb.2006 and 11.Mar.2006, which are on my system, but not on the above archive site. The various threads point to this page: http://www.postgresql.org/docs/8.1/interactive/xaggr.html Specifically the "array_accum" function on that page.