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

[Pavel Stehule]

2010/1/29 Jonah H. Harris :
> On Thu, Jan 28, 2010 at 9:10 AM, Pavel Stehule 
> 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
>

Thank You very much

Pavel

> --
> Jonah H. Harris
>

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[Jonah H. Harris]

On Thu, Jan 28, 2010 at 9:10 AM, Pavel Stehule 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

[HACKERS] can somebody execute this query on Oracle 11.2g and send result?

[Pavel Stehule]

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;

Thank you

Pavel Stehule

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers