pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to get a result in one row
4+ messages / 3 participants
[nested] [flat]
* How to get a result in one row
@ 2006-06-21 15:06 virgi@lettere.unipd.it
0 siblings, 1 reply; 4+ messages in thread
From: virgi@lettere.unipd.it @ 2006-06-21 15:06 UTC (permalink / raw)
To: pgsql-sql
Hi!
I'm using PostgreSQL 7.4.7.
table_c
id | 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.nick
FROM 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to get a result in one row
@ 2006-06-21 18:06 Frank Bax <fbax@sympatico.ca>
parent: virgi@lettere.unipd.it
0 siblings, 2 replies; 4+ messages in thread
From: Frank Bax @ 2006-06-21 18:06 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to get a result in one row
@ 2006-06-21 18:24 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Frank Bax <fbax@sympatico.ca>
1 sibling, 0 replies; 4+ messages in thread
From: Richard Broersma Jr @ 2006-06-21 18:24 UTC (permalink / raw)
To: Frank Bax <fbax@sympatico.ca>; pgsql-sql
> >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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to get a result in one row
@ 2006-06-21 19:15 Frank Bax <fbax@sympatico.ca>
parent: Frank Bax <fbax@sympatico.ca>
1 sibling, 0 replies; 4+ messages in thread
From: Frank Bax @ 2006-06-21 19:15 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-06-21 19:15 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-21 15:06 How to get a result in one row virgi@lettere.unipd.it
2006-06-21 18:06 ` Frank Bax <fbax@sympatico.ca>
2006-06-21 18:24 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-06-21 19:15 ` Frank Bax <fbax@sympatico.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox