Exists a aggregate function (Oracle9) for varchar that generate a comma separated lsit
in [Oracle Help]
|
Prev: how to copy engineer BOM from one organization to other organization
Next: A little help with a query please
From: Norbert Winkler on 25 Jun 2008 10:34 Hello, The Firebird-Database 2.1 contains a LIST function http://www.firebirdsql.org/rlsnotes/Firebird-2.1-ReleaseNotes.pdf ____________________________ The LIST Function Oleg Loa Dmitry Yemanov (v.2.1) This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values. FormatData Manipulation Language (DML) 44 <list function> ::= LIST '(' [ {ALL | DISTINCT} ] <value expression> [',' <delimiter value> ] ')' <delimiter value> ::= { <string literal> | <parameter> | <variable> } ____________________________ Is there any equivalent in ORACLE 9? It seems that there is a COLLECT function in Oracle 10i (http://www.adp-gmbh.ch/blog/2005/march/28.html) But not so easy to use like LIST in FireBird. -- Norbert
From: Terry Dykstra on 25 Jun 2008 14:00 "Norbert Winkler" <norbert.winkler1(a)gmx.de> wrote in message news:1v8hjno5av84.8ejksko31ji4$.dlg(a)40tude.net... > Hello, > > The Firebird-Database 2.1 contains a LIST function > > http://www.firebirdsql.org/rlsnotes/Firebird-2.1-ReleaseNotes.pdf > ____________________________ > The LIST Function > Oleg Loa > Dmitry Yemanov > (v.2.1) This function returns a string result with the concatenated > non-NULL values from a group. It returns > NULL if there are no non-NULL values. > FormatData Manipulation Language (DML) > 44 > <list function> ::= > LIST '(' [ {ALL | DISTINCT} ] <value expression> [',' <delimiter value> > ] ')' > <delimiter value> ::= > { <string literal> | <parameter> | <variable> } > ____________________________ > > Is there any equivalent in ORACLE 9? > > It seems that there is a COLLECT function in Oracle 10i > (http://www.adp-gmbh.ch/blog/2005/march/28.html) > But not so easy to use like LIST in FireBird. > > > -- > Norbert Other databases like SQLAnywhere have a list() function, but Oracle doesn't. Do a google search on 'oracle list function' and you can find examples of user defined functions. -- Terry Dykstra
From: Carlos on 25 Jun 2008 14:06 On 25 jun, 16:34, Norbert Winkler <norbert.winkl...(a)gmx.de> wrote: > Hello, > > The Firebird-Database 2.1 contains a LIST function > > http://www.firebirdsql.org/rlsnotes/Firebird-2.1-ReleaseNotes.pdf > ____________________________ > The LIST Function > Oleg Loa > Dmitry Yemanov > (v.2.1) This function returns a string result with the concatenated > non-NULL values from a group. It returns > NULL if there are no non-NULL values. > FormatData Manipulation Language (DML) > 44 > <list function> ::= > LIST '(' [ {ALL | DISTINCT} ] <value expression> [',' <delimiter value> > ] ')' > <delimiter value> ::= > { <string literal> | <parameter> | <variable> } > ____________________________ > > Is there any equivalent in ORACLE 9? > > It seems that there is a COLLECT function in Oracle 10i > (http://www.adp-gmbh.ch/blog/2005/march/28.html) > But not so easy to use like LIST in FireBird. > > -- > Norbert You may take a look here http://carlosal.wordpress.com/2007/06/26/convertir-tablas-resultsets-a-cadenas/ It's only SQL, but I like it! ;) HTH. Cheers. Carlos.
From: Krzysztof Jamróz on 25 Jun 2008 14:24 Dnia Wed, 25 Jun 2008 16:34:39 +0200, Norbert Winkler napisa�(a): > The LIST Function > Oleg Loa > Dmitry Yemanov > (v.2.1) This function returns a string result with the concatenated > non-NULL values from a group. It returns > NULL if there are no non-NULL values. > > Is there any equivalent in ORACLE 9? > > It seems that there is a COLLECT function in Oracle 10i > (http://www.adp-gmbh.ch/blog/2005/march/28.html) > But not so easy to use like LIST in FireBird. You can check stragg function (it is user defined aggregate function). -- Krzysztof Jamr�z
From: Mark D Powell on 25 Jun 2008 14:40
On Jun 25, 2:24 pm, Krzysztof Jamróz <SpAMkrzyh000J...(a)ZLYo2.pl> wrote: > Dnia Wed, 25 Jun 2008 16:34:39 +0200, Norbert Winkler napisa³(a): > > > The LIST Function > > Oleg Loa > > Dmitry Yemanov > > (v.2.1) This function returns a string result with the concatenated > > non-NULL values from a group. It returns > > NULL if there are no non-NULL values. > > > Is there any equivalent in ORACLE 9? > > > It seems that there is a COLLECT function in Oracle 10i > > (http://www.adp-gmbh.ch/blog/2005/march/28.html) > > But not so easy to use like LIST in FireBird. > > You can check stragg function (it is user defined aggregate function). > > -- > Krzysztof Jamróz Also of possible interest on the topic of creating or extracting comma delimted lists would be the dbms_utility.table_to_comma and comma_to_table procedures for unloading and loading pl/sql tables (arrays). HTH -- Mark D Powell -- |