Re: can somebody execute this query on Oracle 11.2g and send result?

On Thu, Jan 28, 2010 at 9:10 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>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