merging rows into 1 column

I have a table with 2 columns

Input
Co开发者_StackOverflow社区l 1 ---- Col 2
1     ---- aaaa  
1     ---- bbbb
1     ---- cccc
2     ---- dddd
2     ---- eeee
2     ---- ffff
2     ---- gggg

Output

Col 1 ---- Col 2
1     ---- aaaabbbbcccc
2     ---- ddddeeeeffffgggg

I was thinking of doing several self joins, but doesnt seem efficient. Any ideas on how the sql has to be written?

---

Ok, I'll bite. Instead of stragg, try listagg (in 11.2):

create table tst1
(
pid number,
val varchar2(10)
);


insert into tst1 values(1, 'Rec1');
insert into tst1 values(1, 'Rec2');
insert into tst1 values(1, 'Rec3');
insert into tst1 values(2, 'Rec1');
insert into tst1 values(2, 'Rec2');
commit;


select pid, listagg(val, ':') within group(order by val) as "The List"
from tst1
group by pid;

And you get:

pid   The List
1     Rec1:Rec2:Rec3
2     Rec1:Rec2

If you change the order by to "order by val desc" you'd get

pid   The List
1     Rec3:Rec2:Rec1
2     Rec2:Rec1

---

This is a version that will work in Oracle 9i and up.

create table foo (
  key_column number,
  val_column varchar2(4)
);

insert into foo values (1, 'aaaa');
insert into foo values (1, 'bbbb');
insert into foo values (1, 'cccc');
insert into foo values (2, 'dddd');
insert into foo values (2, 'eeee');
insert into foo values (2, 'ffff');
insert into foo values (2, 'gggg');


    select key_column
         , replace(max(sys_connect_by_path(val_column, ',')), ',') combined
      from (select key_column
                 , val_column
                 , row_number() over (partition by key_column order by val_column) cur
                 , row_number() over (partition by key_column order by val_column) - 1 prev
              from foo) foo
  group by key_column 
connect by prior cur = prev and prior key_column = key_column
start with cur = 1;

   key_column | val_column
  --------------------------
            1 | aaaabbbbcccc
            2 | ddddeeeeffffgggg