当前位置:首页 > oracle 列转行函数 listagg

oracle 列转行函数 listagg

点击次数:561  更新日期:2019-04-09

select CUSDEVID,listagg(portcode,';') within group (order by portcode) ports from D_JT_DEVICE_PORT group by CUSDEVID

如果转换的列为nvarchar2类型,则需要先使用to_char,否则查询出来为空白 如下

select CUSDEVID,listagg(to_char(portcode),';') within group (order by portcode) ports from D_JT_DEVICE_PORT group by CUSDEVID


如果报错:ORA-01489: 字符串连接的结果过长,可改为

select CUSDEVID,xmlagg(xmlparse(content to_char(portcode)||',' wellformed) order by portcode).getclobval() ports from D_JT_DEVICE_PORT group by CUSDEVID