将行合并为 1 列

Question

我有一个包含 2 列的表，

Input
Col 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

我正在考虑进行几次自连接，但似乎效率不高。关于如何编写 sql 有什么想法吗？

Answer 1

好吧，我会咬的。而不是 stragg，尝试 listagg （在 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;

你会得到：

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

如果你将 order by 更改为“order by val desc”，你会得到

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

Answer 2

该版本适用于Oracle 9i 及更高版本。

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