表TestA中有如下数据
ID,NAME,TYPE,CARD_ID
1,tom,A,0001
2,tom,A,0002
3,tom,B,0003
4,jack,A,0004
5,jack,A,0005
现在要求的结果是：
1,tom,A,0001,0002
2,tom,B,0003
3,jack,A,0004,0005
------解决方案--------------------
SQL> select distinct
2 yy.name,
3 yy.type,
4 ltrim(first_value(path) over(partition by yy.name,yy.type order by lev desc), ', ') card_id
5 from (
6 select zz.id,
7 zz.name,
8 zz.type,
9 zz.card_id,
10 level lev,
11 sys_connect_by_path(zz.card_id, ', ') path
12 from (
13 select tt.*,
14 tt.name || tt.type || row_number() over(partition by tt.name,tt.type order by tt.name,tt.type) left_rn,
15 tt.name || tt.type || (row_number() over(partition by tt.name,tt.type order by tt.name,tt.type) +1) right_rn
16 from (
17 select 1 as Id, 'tom ' as name, 'A ' as type, '0001 ' as CARD_ID from dual
18 union all
19 select 2 as Id, 'tom ' as name, 'A ' as type, '0002 ' as CARD_ID from dual
20 union all
21 select 3 as Id, 'tom ' as name, 'B ' as type, '0003 ' as CARD_ID from dual
22 union all
23 select 4 as Id, 'jack ' as name, 'A ' as type, '0004 ' as CARD_ID from dual
24 union all
25 select 5 as Id, 'jack ' as name, 'A ' as type, '0005 ' as CARD_ID from dual
26 )tt
27 )zz
28 connect by prior left_rn = right_rn
29 )yy;
NAME TYPE CARD_ID
---- ---- --------------------------------------------
jack A 0005,0004
tom A 0002,0001
tom B 0003