WM_CONCAT 经常使用到行转列上,早期的代码里这个函数用的会比较多,但是可惜在12c中,这个函数已经过期了:
WM_CONCAT is gone in 12c. Use LISTAGG instead
https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9529613900346315631
所以,在后续的开发中,不要再使用这个函数。在MOS中,Oracle也不建议客户使用这个函数,该函数为系统内部使用:
WMSYS.WM_CONCAT Should Not Be Used For Customer Applications, It Is An Internal Function (文档 ID 1336219.1)
https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=165418151757953&id=1336219.1&_afrWindowMode=0&_adf.ctrl-state=9gq0498j_4
那PG中有没有类似的函数呢?答案是肯定的:string_agg.
pg可以指定其中的分隔符:
test=# select id,sum(score),string_agg(name,'-') from t_concat group by id order by id; id | sum | string_agg ----+-----+--------------------------------- 1 | 399 | yuwen-shuxue-yingyu-wuli-huaxue 2 | 406 | yuwen-shuxue-yingyu-wuli-huaxue (2 rows) test=# select id,sum(score),string_agg(name,'||') from t_concat group by id order by id; id | sum | string_agg ----+-----+------------------------------------- 1 | 399 | yuwen||shuxue||yingyu||wuli||huaxue 2 | 406 | yuwen||shuxue||yingyu||wuli||huaxue (2 rows) test=#在Oracle官方文档中,从11gr2开始,建议使用listagg代替wm_concat:
https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030
col names format a60; select id,sum(score),listagg(name,'-') within group (order by score ) as "names"from t_concat group by id order by id; SQL> SQL> select id,sum(score),listagg(name,'-') within group (order by score ) as "names"from t_concat group by id order by id; ID SUM(SCORE) names ---------- ---------- ------------------------------------------------------------ 1 399 yingyu-huaxue-wuli-shuxue-yuwen 2 406 yingyu-huaxue-wuli-shuxue-yuwen SQL> select id,sum(score),listagg(name) within group (order by name ) as "names"from t_concat group by id order by id; ID SUM(SCORE) names ---------- ---------- ------------------------------------------------------------ 1 399 huaxueshuxuewuliyingyuyuwen 2 406 huaxueshuxuewuliyingyuyuwen SQL>分隔符不是必选参数。PG的string_agg的分隔符为必选参数:
test=# select id,sum(score),string_agg(name) from t_concat group by id order by id; ERROR: function string_agg(character varying) does not exist LINE 1: select id,sum(score),string_agg(name) from t_concat group by... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. test=#参考链接:
http://www.dba-oracle.com/t_wm_concat_sql_function.htm
https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030
https://yq.aliyun.com/articles/5061