oracle行转列，列转行函数的使用（listagg，xmlagg）

野性酷女 2022-10-09 12:16 436阅读 0赞

一、行转列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' ;

![cacf0bbd0542062650f232beb92a66d1.png][]

2、使用listagg() WITH GROUP()将多行合并成一行（比较常用）

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

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

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

![4ed175a52bc0f77f02ef573fc5d8cf6f.png][]

3、使用listagg() width group() over将多行记录在一行显示（不常用）

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

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'

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

![c5c59fd2da1c12a191f23e36e22d9c2e.png][]

二、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

转自：[https://blog.csdn.net/sinat\_36257389/article/details/81004843][https_blog.csdn.net_sinat_36257389_article_details_81004843]，转载请注明来源。

listagg

SELECTT .DEPTNO,listagg (T .ENAME, ',') WITHIN GROUP (ORDER BY T .ENAME)  over(PARTITION BY T .DEPTNO)FROMSCOTT.EMP TWHERET .DEPTNO = '20'

[https://www.cnblogs.com/damoblog/p/12458673.html][https_www.cnblogs.com_damoblog_p_12458673.html]

分类: [oracle数据库][oracle]

[cacf0bbd0542062650f232beb92a66d1.png]: /images/20221005/965c3621df634f7792eca9cd6df36ec7.png
[48304ba5e6f9fe08f3fa1abda7d326ab.png]: /images/20221005/e31ba21e1be64d98b9d872983ee02996.png
[4ed175a52bc0f77f02ef573fc5d8cf6f.png]: /images/20221005/5356ad377256461e9242733ef2fdace0.png
[c5c59fd2da1c12a191f23e36e22d9c2e.png]: /images/20221005/333441eb0c9f4f78ad0f1ff02d582673.png
[https_blog.csdn.net_sinat_36257389_article_details_81004843]: https://blog.csdn.net/sinat_36257389/article/details/81004843
[https_www.cnblogs.com_damoblog_p_12458673.html]: https://www.cnblogs.com/damoblog/p/12458673.html
[oracle]: https://www.cnblogs.com/damoblog/category/1149398.html