oracle中分组后，把一组中一列的多行值合并成一行

自己在网上碰到的问题，自己尝试解决，现在给出我的解决方法，供参考。

数据准备：

create table EMP
(
  ename  VARCHAR2(10),
  deptno NUMBER
);

insert into EMP (ename, deptno)
values ('a', 10);
insert into EMP (ename, deptno)
values ('b', 10);
insert into EMP (ename, deptno)
values ('c', 10);
insert into EMP (ename, deptno)
values ('d', 20);
insert into EMP (ename, deptno)
values ('e', 20);
insert into EMP (ename, deptno)
values ('f', 20);
insert into EMP (ename, deptno)
values ('g', 20);
insert into EMP (ename, deptno)
values ('h', 20);
insert into EMP (ename, deptno)
values ('i', 30);
insert into EMP (ename, deptno)
values ('j', 30);
insert into EMP (ename, deptno)
values ('k', 30);
insert into EMP (ename, deptno)
values ('l', 30);
insert into EMP (ename, deptno)
values ('m', 30);
insert into EMP (ename, deptno)
values ('n', 30);
commit;

--输出结果：

with result_emp (ename,deptno,rn)as (
     select ename,deptno, row_number() over(partition by deptno order by ename) rn from emp
),
merge_result(deptno,name_merge,rn) as (
    select deptno,'' as name ,0 as rn from (select distinct deptno from emp)
    union all
    select r.deptno, ltrim(m.name_merge||','||r.ename,','), r.rn
          from result_emp r, merge_result m
    where r.deptno = m.deptno
    and r.rn-1 = m.rn
)
select * from merge_result a right join
(select deptno ,max(rn)as rn from merge_result group by deptno) b on a.deptno = b.deptno and a.rn = b.rn;

结果 看看是什么样子的。不过这里使用with as的递归用法，可以上网上找找研究下（推荐此人的文章）：http://blog.sina.com.cn/s/blog_4cef5c7b01016jun.html

当然也可以使用connect by 递归方法，此方法有点烦，不过共同的技巧都是使用了row_number()的方法。

以下 是connect by 的方法：

数据准备：

create table TESTA
(
  id   NUMBER not null,
  name VARCHAR2(10),
  mark VARCHAR2(10)
)

create table TESTB
(
  betrack NUMBER,
  track   NUMBER
)

insert into TESTA (id, name, mark)
values (1001, 'jam', 'ui');
insert into TESTA (id, name, mark)
values (1002, 'jack', 'kk');
insert into TESTA (id, name, mark)
values (1003, 'tim', 'ww');
insert into TESTA (id, name, mark)
values (1004, 'hls', 'jj');
insert into TESTA (id, name, mark)
values (1444, '1111', '1111');
commit;

insert into TESTB (betrack, track)
values (1001, 1002);
insert into TESTB (betrack, track)
values (1001, 1003);
insert into TESTB (betrack, track)
values (1004, 1002);
insert into TESTB (betrack, track)
values (1002, 1003);
commit;

结果：  ID       TRACK_MERGE

1002         (1003,tim,ww)

1004         (1002,jack,kk)

1001          (1003,tim,ww)-(1002,jack,kk)                   

with
  first_re (id, track ,merge_c) as (
    select a.id,a.track, ('('||b.id||','||b.name||','||b.mark||')') as merge_c
            from
            (select  ta.id as id ,tb.track as track 
            from TESTA ta left join TESTB tb on ta.id=tb.betrack 
            WHERE EXISTS (SELECT * FROM TESTB tb WHERE ta.id=tb.betrack )) a 
             left join TESTA b on  a.track = b.id 
  ),
  t_mer (id ,mergec) as(
    select id,merge_result from 
      (select id, merge_result, row_number() over(partition by id order by id,cur_lvl desc) as lv

      FROM (SELECT f.id as id,
             f.rank as rank,
             level as cur_lvl,
             ltrim(sys_connect_by_path(f.merge_c,'-'),'-') merge_result
        FROM
        (select t.id as id,
                t.merge_c as merge_c, 
                row_number() over(partition by t.id order by t.id) rank 
        from first_re t order by t.id) f
        connect by f.id = prior f.id and f.rank-1=prior f.rank))
      where lv = 1
)
select * from t_mer;