Prev: SQL-- Please help me :)
Next: Last not null value from previous rows
From: dn.perl on 26 Jan 2010 11:07

Is it possible to concatenate records in a field via Group By clause
of an SQL statement?

Untested script is :
create table t1(country varchar2(32), state varchar2(32), city varchar2
(32)) ;
insert into t1 values ('USA', 'CA', 'Sacramento') ;
insert into t1 values ('USA', 'CA', 'San Jose') ;
insert into t1 values ('USA', 'CA', 'three') ;
insert into t1 values ('USA', 'CA', 'four') ;
insert into t1 values ('USA', 'CA', 'Fremont') ;
insert into t1 values ('Canada', 'OT', 'Ottawa') ;
insert into t1 values ('Canada', 'OT', 'Toronto') ;

Expected output for the SQL statement would be :
USA CA Sacramento, San Jose, three, four, Fremont
Canada OT Ottawa, Toronto


Thanks in advance.

From: Michel Cadot on 26 Jan 2010 11:33

<dn.perl(a)gmail.com> a �crit dans le message de news: a122dd75-b1d1-40cb-ac85-fdfb42510376(a)e37g2000yqn.googlegroups.com...
|
| Is it possible to concatenate records in a field via Group By clause
| of an SQL statement?
|
| Untested script is :
| create table t1(country varchar2(32), state varchar2(32), city varchar2
| (32)) ;
| insert into t1 values ('USA', 'CA', 'Sacramento') ;
| insert into t1 values ('USA', 'CA', 'San Jose') ;
| insert into t1 values ('USA', 'CA', 'three') ;
| insert into t1 values ('USA', 'CA', 'four') ;
| insert into t1 values ('USA', 'CA', 'Fremont') ;
| insert into t1 values ('Canada', 'OT', 'Ottawa') ;
| insert into t1 values ('Canada', 'OT', 'Toronto') ;
|
| Expected output for the SQL statement would be :
| USA CA Sacramento, San Jose, three, four, Fremont
| Canada OT Ottawa, Toronto
|
|
| Thanks in advance.
|

Yes, using T. Kyte's STRAGG function or WM_CONCAT one.

Regards
Michel


From: Shakespeare on 26 Jan 2010 14:43
Op 26-1-2010 17:07, dn.perl(a)gmail.com schreef:
>
> Is it possible to concatenate records in a field via Group By clause
> of an SQL statement?
>
> Untested script is :
> create table t1(country varchar2(32), state varchar2(32), city varchar2
> (32)) ;
> insert into t1 values ('USA', 'CA', 'Sacramento') ;
> insert into t1 values ('USA', 'CA', 'San Jose') ;
> insert into t1 values ('USA', 'CA', 'three') ;
> insert into t1 values ('USA', 'CA', 'four') ;
> insert into t1 values ('USA', 'CA', 'Fremont') ;
> insert into t1 values ('Canada', 'OT', 'Ottawa') ;
> insert into t1 values ('Canada', 'OT', 'Toronto') ;
>
> Expected output for the SQL statement would be :
> USA CA Sacramento, San Jose, three, four, Fremont
> Canada OT Ottawa, Toronto
>
>
> Thanks in advance.
>

11g has a LISTAGG function for this!

Shakespeare
 | 
Pages: 1
Prev: SQL-- Please help me :)
Next: Last not null value from previous rows