行转列:
concat函数:
select wmsys.wm_concat(column) from test group id;
listagg函数:
select listagg(name,',')within group(order by age)name from emp;
Clob行转列:
拼接起来以“,”分割:
select XMLAGG(XMLELEMENT(E, column|| ',')).EXTRACT('//text()').getclobval() as "column" from test group id;
字段不为空:
select XMLAGG(XMLELEMENT(E, column|| ',')).EXTRACT('//text()').getclobval() as "column" from test where column is not null and dbms_lob.getlength(column) <> 0 group id;
trim函数去除空格:
select trim(XMLAGG(XMLELEMENT(E, column|| ',')).EXTRACT('//text()').getclobval()) as "column" from test where column is not null and dbms_lob.getlength(column) <> 0 group id;