最近在网上老是遇到一些同行求将多条记录按规则显示为一条的信息,现整理如下:
create table test_1
(
col1 varchar2(4),
col2 varchar2(4),
col3 varchar2(4),
col4 varchar2(4)
);
insert into test_1 values('123','t1','s','p1');
insert into test_1 values('123','t1','s','p2');
insert into test_1 values('124','c1','s','p3');
insert into test_1 values('124','c1','s','p4');
SQL> select * from test_1;
COL1 COL2 COL3 COL4
---- ---- ---- ----
123 t1 s p1
123 t1 s p2
124 c1 s p3
124 c1 s p4
下面的语句实现将结果变为 123 t1 s p1,p2
124 c1 s p3,p4
SQL>
SQL> select col1, col2, col3, substr(max(sys_connect_by_path(col4, ',')), 2) aa
2 from (select col1,
3 col2,
4 col3,
5 col4,
6 col1 || col2 || col3 || col5 col5,
7 col1 || col2 || col3 || col6 col6
8 from (select col1,
9 col2,
10 col3,
11 col4,
12 rank() over(partition by col1, col2, col3 order by col4) col5,
13 rank() over(partition by col1, col2, col3 order by col4) - 1 col6
14 from test_1))
15 start with substr(col5, -1) = 1
16 connect by prior col5 = col6
17 group by col1, col2, col3
18 ;
COL1 COL2 COL3 AA
---- ---- ---- --------------------------------------------------------------------------------
123 t1 s p1,p2
124 c1 s p3,p4