On Thu, Jan 28, 2010 at 9:10 AM, Pavel Stehule <[email protected]>wrote:
> Hello,
>
> I can't to install Oracle, and need to know result.
>
> CREATE TABLE foo(a varchar(10), b varchar(10));
>
> INSERT INTO foo VALUES('aaa',',');
> INSERT INTO foo VALUES('bbb',';');
> INSERT INTO foo VALUES('ccc','+');
>
> SELECT listagg(a,b) FROM foo;
>
That's not how listagg works.
The syntax is listagg(expression [, delimiter]) WITHIN GROUP (order by
clause) [OVER partition clause]
If a delimiter is defined, it must be a constant.
Query: SELECT listagg(a, ',') WITHIN GROUP (ORDER BY a) FROM foo;
Result: aaa,bbb,ccc
Query: SELECT listagg(a, ';') WITHIN GROUP (ORDER BY a) FROM foo;
Result: aaa;bbb;ccc
Query: SELECT listagg(a, '+') WITHIN GROUP (ORDER BY a) FROM foo;
Result: aaa+bbb+ccc
--
Jonah H. Harris
