oracle行转列,列转行函数的使用(listagg,xmlagg)
一、行转列listagg函数:
场景:这里的表数据使用的是oracle默认的scott账户下的emp(员工)表。
规范写法 : LISTAGG(字段, 连接符) WITHIN GROUP (ORDER BY 字段)
通常情况下,LISTAGG是满足需要的,LISTAGG 返回的是一个varchar2类型的数据,最大字节长度为4000。
所以,在实际开发中,我们可能会遇到一个问题,连接长度过长。在这个时候,我们需要将LISTAGG函数改成XMLAGG函数。XMLAGG返回的类型为CLOB,最大字节长度为32767。LISTAGG例子:
1、使用条件查询部门号为20号的员工:
-- 查询部门为20的员工列表
SELECT t.DEPTNO,t.ENAME FROM SCOTT.EMP t where t.DEPTNO = '20' ;
2、使用listagg() WITH GROUP()将多行合并成一行(比较常用)
复制代码
SELECT
T.DEPTNO,
listagg (T.ENAME, ',') WITHIN GROUP (ORDER BY T.ENAME) names
FROM
SCOTT.EMP T
WHERE
T.DEPTNO = '20'
GROUP BY
T.DEPTNO
3、使用listagg() width group() over将多行记录在一行显示(不常用)
SELECT
T .DEPTNO,
listagg (T .ENAME, ',') WITHIN GROUP (ORDER BY T .ENAME) over(PARTITION BY T .DEPTNO)
FROM
SCOTT.EMP T
WHERE
T .DEPTNO = '20'
二、XMLAGG函数的例子:
XMLAGG(XMLPARSE(CONTENT BSO.ID || ',' WELLFORMED) ORDER BY BSO.ID).GETCLOBVAL()
规划写法: XMLAGG(XMLPARSE(CONTENT 字段 || 字符串 WELLFORMED) ORDER BY 字段).GETCLOBVAL()
三、对于mysql相同的效果实现,可以使用group_concat() 函数,详情可参考:
mysql相同效果的实现 https://blog.csdn.net/sinat_36257389/article/details/95052001
PostgreSQL 相同效果的实现 https://blog.csdn.net/sinat_36257389/article/details/95611686