pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMerging rows into one result?
5+ messages / 4 participants
[nested] [flat]
* Merging rows into one result?
@ 2006-03-11 16:31 Jesper K. Pedersen <jkp@solnet.homeip.net>
0 siblings, 3 replies; 5+ messages in thread
From: Jesper K. Pedersen @ 2006-03-11 16:31 UTC (permalink / raw)
To: pgsql-sql
Is it possible to use SQL to merge data into one result?
A theorethical example to explain:
tbl_test (
id integer,
information varchar(25))
id | information
---+--------------
1 | Yo
2 | Go away
1 | Stay put
3 | Greetings
Please note id is not unique and not a primary key.
and I wonder if there is any functions to "merge" data (sort of
concat'ing).
A normal: select information from tbl_test where id=1
would result in the rows
Yo
Stay put
I would like a single row result in the format of:
Yo Stay put
Any ideas on this?
Best regards
Jesper K. Pedersen
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Merging rows into one result?
@ 2006-03-11 16:43 Andreas Kretschmer <akretschmer@spamfence.net>
parent: Jesper K. Pedersen <jkp@solnet.homeip.net>
2 siblings, 1 reply; 5+ messages in thread
From: Andreas Kretschmer @ 2006-03-11 16:43 UTC (permalink / raw)
To: pgsql-sql
Jesper K. Pedersen <jkp@solnet.homeip.net> schrieb:
> Is it possible to use SQL to merge data into one result?
>
> A theorethical example to explain:
>
> tbl_test (
> id integer,
> information varchar(25))
>
> id | information
> ---+--------------
> 1 | Yo
> 2 | Go away
> 1 | Stay put
> 3 | Greetings
>
> Please note id is not unique and not a primary key.
>
> and I wonder if there is any functions to "merge" data (sort of
> concat'ing).
> A normal: select information from tbl_test where id=1
> would result in the rows
> Yo
> Stay put
>
> I would like a single row result in the format of:
> Yo Stay put
Yes, of corse, this is possible. You need a own aggregate-function. A
similar example for this task can you find here:
http://www.zigo.dhs.org/postgresql/#comma_aggregate
I think, it is very simple to rewrite this example for your purpose.
HTH, 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°
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Merging rows into one result?
@ 2006-03-11 16:48 Volkan YAZICI <yazicivo@ttnet.net.tr>
parent: Jesper K. Pedersen <jkp@solnet.homeip.net>
2 siblings, 0 replies; 5+ messages in thread
From: Volkan YAZICI @ 2006-03-11 16:48 UTC (permalink / raw)
To: Jesper K. Pedersen <jkp@solnet.homeip.net>; +Cc: pgsql-sql
Hi,
On Mar 11 05:31, Jesper K. Pedersen wrote:
> Is it possible to use SQL to merge data into one result?
test=# SELECT id, info FROM concat_t;
id | info
----+------
1 | A
2 | B
1 | AA
3 | C
1 | D
1 | DD
(6 rows)
test=# SELECT array_to_string(ARRAY(SELECT info FROM concat_t WHERE id = 1), ' ');
array_to_string
-----------------
A AA D DD
(1 row)
HTH
Regards.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Merging rows into one result?
@ 2006-03-11 16:58 Jesper K. Pedersen <jkp@solnet.homeip.net>
parent: Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 0 replies; 5+ messages in thread
From: Jesper K. Pedersen @ 2006-03-11 16:58 UTC (permalink / raw)
To: pgsql-sql
On Sat, 11 Mar 2006 17:43:37 +0100
Andreas Kretschmer <akretschmer@spamfence.net> wrote:
> Jesper K. Pedersen <jkp@solnet.homeip.net> schrieb:
>
> > Is it possible to use SQL to merge data into one result?
> >
> > A theorethical example to explain:
> >
> > tbl_test (
> > id integer,
> > information varchar(25))
> >
> > id | information
> > ---+--------------
> > 1 | Yo
> > 2 | Go away
> > 1 | Stay put
> > 3 | Greetings
> >
> > Please note id is not unique and not a primary key.
> >
> > and I wonder if there is any functions to "merge" data (sort of
> > concat'ing).
> > A normal: select information from tbl_test where id=1
> > would result in the rows
> > Yo
> > Stay put
> >
> > I would like a single row result in the format of:
> > Yo Stay put
>
> Yes, of corse, this is possible. You need a own aggregate-function. A
> similar example for this task can you find here:
>
> http://www.zigo.dhs.org/postgresql/#comma_aggregate
>
> I think, it is very simple to rewrite this example for your purpose.
>
The comma aggregate worked like a charm.
Thank's
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Merging rows into one result?
@ 2006-03-11 17:00 Christopher Browne <cbbrowne@acm.org>
parent: Jesper K. Pedersen <jkp@solnet.homeip.net>
2 siblings, 0 replies; 5+ messages in thread
From: Christopher Browne @ 2006-03-11 17:00 UTC (permalink / raw)
To: pgsql-sql
Centuries ago, Nostradamus foresaw when jkp@solnet.homeip.net ("Jesper K. Pedersen") would write:
> Is it possible to use SQL to merge data into one result?
>
> A theorethical example to explain:
>
> tbl_test (
> id integer,
> information varchar(25))
>
> id | information
> ---+--------------
> 1 | Yo
> 2 | Go away
> 1 | Stay put
> 3 | Greetings
>
> Please note id is not unique and not a primary key.
>
> and I wonder if there is any functions to "merge" data (sort of
> concat'ing).
> A normal: select information from tbl_test where id=1
> would result in the rows
> Yo
> Stay put
>
> I would like a single row result in the format of:
> Yo Stay put
>
> Any ideas on this?
Sure, you could create a custom aggregate to append them using spaces.
Look in the PostgreSQL documentation under "CREATE AGGREGATE." If you
check the online version at PostgreSQL.org, there are comments showing
examples...
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://linuxdatabases.info/info/wp.html
--Despite Pending :Alarm--
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-03-11 17:00 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-11 16:31 Merging rows into one result? Jesper K. Pedersen <jkp@solnet.homeip.net>
2006-03-11 16:43 ` Andreas Kretschmer <akretschmer@spamfence.net>
2006-03-11 16:58 ` Jesper K. Pedersen <jkp@solnet.homeip.net>
2006-03-11 16:48 ` Volkan YAZICI <yazicivo@ttnet.net.tr>
2006-03-11 17:00 ` Christopher Browne <cbbrowne@acm.org>
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