SQL查询多条记录显示为一条的处理办法

最近在网上老是遇到一些同行求将多条记录按规则显示为一条的信息，现整理如下：

 

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