表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
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
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