单列多行转单行单列合并oracle_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 m.ID,RTRIM(m.names,chr(10)) names

FROM(

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)) 去除最后一行顽固的回车