oracle 查询返回多行合成一行

 

oracle wm_concat函数，用于列转行，逗号分隔

SELECT n_sec_code, wmsys.wm_concat (c_researcher_code) as result

FROM m_researcher_stock_rel

GROUP BY n_sec_code

 

效果1 : 行转列

SQL> select wm_concat(name) from test;

WM_CONCAT(NAME)

-------------------------------------------------------------------------

a,b,c,d,e

效果2: 把结果里的逗号替换成"|"

SQL> select replace(wm_concat(name),',','|') from test;

REPLACE(WM_CONCAT(NAME),',','|')

-----------------------------------------------------------------------

a|b|c|d|e

效果3:按ID分组合并name

SQL> select id,wm_concat(name) name from test group by id;

ID NAME

---------- ------------------------------

1 a,b,c

2 d,e