Oracle 多行转一列

---

1、WM_CONCAT()

合并的字符串要求小于4000字节。

SELECT
t.ID,
REPLACE(wm_concat(TO_CHAR(t.name),','CHR(13)) as names
FROM testtable t
GROUP BY t.ID

2、LISTAGG()

合并的字符串要求小于4000字节。

SELECT
t.ID,
listagg(t.name,CHR(13)) within group(order by 序号) as names
FROM testtable t
GROUP BY t.ID

3、XMLAGG()

大于4000字节的行转化为列

SELECT
t.ID,
RTRIM(RTRIM(XMLAGG(XMLELEMENT(e,t.name|| chr(13)).extract('//text()')ORDER BY 序号).getClobVal(),chr(10)),chr(13))names
FROM testtable t
GROUP BY t.ID

多条内容分行合并 并且用rtrim(rtrim(names,char(10)),chr(13))去除最后一行顽固的回车。

---