WM_CONCAT 行列转换函数简单实用

CREATE TABLE TEST(ID NUMBER,NAME VARCHAR2(20));

INSERT INTO TEST VALUES(123,'AAA');
INSERT INTO TEST VALUES(123,'BBB');
INSERT INTO TEST VALUES(123,'CCC');
INSERT INTO TEST VALUES(234,'DDD');
INSERT INTO TEST VALUES(234,'EEE');
COMMIT;
--逗号 分隔符
SELECT TO_CHAR(WM_CONCAT(NAME)) FROM TEST;
1 AAA,BBB,CCC,DDD,EEE

--竖杠分隔符，显示为CLOB类型，不直观
SELECT REPLACE(WM_CONCAT(NAME),',','|') FROM TEST;
1 <CLOB>

--竖杠分隔符，显示为char类型，直观
SELECT TO_CHAR(REPLACE(WM_CONCAT(NAME),',','|')) FROM TEST;
1 AAA|BBB|CCC|DDD|EEE

--分组
select id,to_char(wm_concat(name)) name from test group by id;
1 123 AAA,CCC,BBB
2 234 DDD,EEE