string concatenation via Group By

#1

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.

#2

<dn.perl (AT) gmail (DOT) com> a écrit dans le message de news: a122dd75-b1d1-40cb-ac85-fdfb42510376...oglegroups.com...

Quote:

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

#3

Op 26-1-2010 17:07, dn.perl (AT) gmail (DOT) com schreef:

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

string concatenation via Group By

comp.databases.oracle.misc comp.databases.oracle.misc

string concatenation via Group By - 01-26-2010 , 11:07 AM

Re: string concatenation via Group By - 01-26-2010 , 11:33 AM

Re: string concatenation via Group By - 01-26-2010 , 02:43 PM