Oracle将多条记录的同一字段值连接为一个字符串

 

oracle 将多条记录的某一字段，连接为一个字符串

CREATE TABLE t_test
(
  ID NUMBER PRIMARY KEY,
  grade VARCHAR2(20),
  uname VARCHAR2(20)
);

insert into t_test values(1, 'A', 'zhang1');
insert into t_test values(2, 'A', 'wang2');
insert into t_test values(3, 'A', 'li3');
insert into t_test values(4, 'A', 'wang2');
insert into t_test values(5, 'B', 'zhao4');
insert into t_test values(6, 'A', 'liu5');
insert into t_test values(7, 'B', 'qi6');
insert into t_test values(8, 'B', 'lu7');

SELECT grade,
       SUBSTR(MAX(SYS_CONNECT_BY_PATH(uname, '，')), 2) uname
  FROM (SELECT grade,
               uname,
               rn,
               LEAD(rn) OVER(PARTITION BY grade ORDER BY rn) rn1
          FROM (SELECT grade,
                       uname,
                       ROW_NUMBER() OVER(ORDER BY uname) rn
                  FROM t_test))
 START WITH rn1 IS NULL
CONNECT BY rn1 = PRIOR rn
 GROUP BY grade;

       GRADE    UNAME
1    A    zhang1，wang2，wang2，liu5，li3
2    B    zhao4，qi6，lu7