[Oracle]多行數據合併，並分號分隔，LISTAGG

 最近常常遇到要寄送告警資料給同仁，常常需要整理一些郵件，分享一個好方法

CREATE TABLE TEST_LISTAGG(
 DEPID      VARCHAR2(10),
 USER_NAME  VARCHAR2(10),
 MAIL       VARCHAR2(50)
)



INSERT INTO TEST_LISTAGG(DEPID,USER_NAME,MAIL)VALUES('A01','Judy','Judy@1234');
INSERT INTO TEST_LISTAGG(DEPID,USER_NAME,MAIL)VALUES('A01','Candy','Candy@1234');
INSERT INTO TEST_LISTAGG(DEPID,USER_NAME,MAIL)VALUES('A02','Teddy','Teddy@1234');
INSERT INTO TEST_LISTAGG(DEPID,USER_NAME,MAIL)VALUES('A03','Andy','Andy@1234');

SELECT * FROM TEST_LISTAGG;

A01	Judy	Judy@1234
A01	Candy	Candy@1234
A02	Teddy	Teddy@1234
A03	Andy	Andy@1234


SELECT DEPID, 
       LISTAGG(MAIL,';') WITHIN GROUP(ORDER BY DEPID) AS NEW_MAIL
  FROM TEST_LISTAGG
 GROUP BY DEPID;
 

A01	Candy@1234;Judy@1234
A02	Teddy@1234
A03	Andy@1234