Thread: Merging rows into one result?
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 rowsYoStay put I would like a single row result in the format of:Yo Stay put Any ideas on this? Best regards Jesper K. Pedersen
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°
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.
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
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--