2.1_4 Oralce LISTAGG函数【列转行 多列 -＞ 一列(指定分隔符)】

已于 2022-05-10 12:32:35 修改
阅读量4.5k 收藏 2
点赞数
分类专栏： # 2.1 Oracle 文章标签： 数据库 dba oracle
于 2020-08-21 15:54:26 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qq_43529621/article/details/108143822
版权

目录

相关链接

前言

碰到此类情况 ，需要将多列数据合并成为一列时，可以采用LISTAGG聚合函数

NATION|CITY     |  	  => 	NATION|CITIES                    |
------|---------|  	  =>	------|--------------------------|
China |Guangzhou|  	  =>	China |Beijing,Guangzhou,Shanghai|
China |Shanghai |  	  =>	Japan |Tokyo                     |
China |Beijing  |  	  =>	USA   |Bostom,New York           |
USA   |New York |
USA   |Bostom   |
Japan |Tokyo    |

（wc_concat也可以达到同样效果，但测试用的11g版本好像不支持wmsys.wm_concat函数，没有深入研究）

  • Oracle 环境
--执行SQL
SELECT * FROM V$VERSION;

--返回结果
BANNER                                                                      |
----------------------------------------------------------------------------|
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production|
PL/SQL Release 11.2.0.4.0 - Production                                      |
CORE 11.2.0.4.0 Production                                                  |
TNS for Linux: Version 11.2.0.4.0 - Production                              |
NLSRTL Version 11.2.0.4.0 - Production                                      |

LISTAGG()语法

  • 基础用法 - 参数
    • column ：待合并字段
    • separator ： 分隔符，两端需要加单引号
    • sortCol：组内排序字段
LISTAGG(${column},'${separator}') WITHIN GROUP(ORDER BY ${sortCol})

注：LISTAGG属于聚合函数，只能和其他聚合函数(SUM,AVG等)，或GROUP BY字段一起查询

--执行SQL
SELECT 
	T.SAL,
	LISTAGG(T.ENAME,',') WITHIN GROUP(ORDER BY T.ENAME)
FROM SCOTT.EMP T

--返回结果
org.jkiss.dbeaver.model.sql.DBSQLException: SQL 错误 [937] [42000]: ORA-00937: 不是单组分组函数
  • 进阶用法 ( + 开窗函数 )
LISTAGG(${column},'${separator}') WITHIN GROUP(ORDER BY ${sortCol}) OVER (PARTITION BY 分组字段)

实例1 SCOTT.EMP

  • 查看表
--执行SQL
SELECT * FROM SCOTT.EMP

--返回结果
EMPNO|ENAME |JOB      |MGR |HIREDATE           |SAL |COMM|DEPTNO|
-----|------|---------|----|-------------------|----|----|------|
 7369|SMITH |CLERK    |7902|1980-12-17 00:00:00| 800|    |    20|
 7499|ALLEN |SALESMAN |7698|1981-02-20 00:00:00|1600| 300|    30|
 7521|WARD  |SALESMAN |7698|1981-02-22 00:00:00|1250| 500|    30|
 7566|JONES |MANAGER  |7839|1981-04-02 00:00:00|2975|    |    20|
 7654|MARTIN|SALESMAN |7698|1981-09-28 00:00:00|1250|1400|    30|
 7698|BLAKE |MANAGER  |7839|1981-05-01 00:00:00|2850|    |    30|
 7782|CLARK |MANAGER  |7839|1981-06-09 00:00:00|2450|    |    10|
 7788|SCOTT |ANALYST  |7566|1987-04-19 00:00:00|3000|    |    20|
 7839|KING  |PRESIDENT|    |1981-11-17 00:00:00|5000|    |    10|
 7844|TURNER|SALESMAN |7698|1981-09-08 00:00:00|1500|   0|    30|
 7876|ADAMS |CLERK    |7788|1987-05-23 00:00:00|1100|    |    20|
 7900|JAMES |CLERK    |7698|1981-12-03 00:00:00| 950|    |    30|
 7902|FORD  |ANALYST  |7566|1981-12-03 00:00:00|3000|    |    20|
 7934|MILLER|CLERK    |7782|1982-01-23 00:00:00|1300|    |    10|

练习1.1

  • 不分组，按逗号分隔，组内按员工姓名排序
--执行SQL
SELECT 
	LISTAGG(T.ENAME,',') WITHIN GROUP(ORDER BY T.ENAME) AS ENAMES
FROM SCOTT.EMP T

--返回结果
ENAMES                                                                             |
-----------------------------------------------------------------------------------|
ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER,WARD|

练习1.2

  • 按JOB分组，按逗号分隔，组内按员工姓名(ENAME)排序
--执行SQL
SELECT 
	T.JOB,
	LISTAGG(T.ENAME,',') WITHIN GROUP(ORDER BY T.ENAME) AS ENAMES
FROM SCOTT.EMP T
GROUP BY T.JOB

--返回结果
 JOB     |ENMAES                                         |
---------|-----------------------------------------------|
ANALYST  |FORD,SCOTT                                     |
CLERK    |ADAMS,JAMES,MILLER,SMITH                       |
MANAGER  |BLAKE,CLARK,JONES                              |
PRESIDENT|KING                                           |
SALESMAN |ALLEN,MARTIN,TURNER,WARD                       |

练习1.3

  • 按JOB分组，按逗号分隔，组内按工资(SAL)排序
--执行SQL
SELECT 
	T.JOB,
	LISTAGG(T.ENAME,',') WITHIN GROUP(ORDER BY T.SAL) AS ENMAES,
	LISTAGG(T.SAL,',') WITHIN GROUP(ORDER BY T.SAL) AS SALS
FROM SCOTT.EMP T
GROUP BY T.JOB

--返回结果
JOB      |ENMAES                                       |SALS                                       |
---------|---------------------------------------------|-------------------------------------------|
ANALYST  |FORD,SCOTT                                   |3000,3000                                  |
CLERK    |SMITH,JAMES,ADAMS,MILLER                     |800,950,1100,1300                          |
MANAGER  |CLARK,BLAKE,JONES                            |2450,2850,2975                             |
PRESIDENT|KING                                         |5000                                       |
SALESMAN |MARTIN,WARD,TURNER,ALLEN                     |1250,1250,1500,1600                        |

实例2 虚表DUAL

  • 查看表
--执行SQL
SELECT 'China' nation ,'Guangzhou' city FROM dual UNION ALL 
SELECT 'China' nation ,'Shanghai' city FROM dual UNION ALL 
SELECT 'China' nation ,'Beijing' city FROM dual UNION ALL 
SELECT 'USA' nation ,'New York' city FROM dual UNION ALL 
SELECT 'USA' nation ,'Bostom' city FROM dual UNION ALL 
SELECT 'Japan' nation ,'Tokyo' city FROM dual

--返回结果
NATION|CITY     |
------|---------|
China |Guangzhou|
China |Shanghai |
China |Beijing  |
USA   |New York |
USA   |Bostom   |
Japan |Tokyo    |

练习2.1

  • 按国家分组,按逗号分隔，组内按城市名(CITY)称排序
--执行SQL
WITH temp AS( 
  SELECT 'China' nation ,'Guangzhou' city FROM dual UNION ALL 
  SELECT 'China' nation ,'Shanghai' city FROM dual UNION ALL 
  SELECT 'China' nation ,'Beijing' city FROM dual UNION ALL 
  SELECT 'USA' nation ,'New York' city FROM dual UNION ALL 
  SELECT 'USA' nation ,'Bostom' city FROM dual UNION ALL 
  SELECT 'Japan' nation ,'Tokyo' city FROM dual  
) 
SELECT 
	nation,
	LISTAGG(city,',') WITHIN GROUP (ORDER BY city) AS CITIES
FROM temp 
GROUP BY nation

--返回结果
NATION|CITIES                                   |
------|-----------------------------------------|
China |Beijing,Guangzhou,Shanghai               |
Japan |Tokyo                                    |
USA   |Bostom,New York                          |

练习2.2 LISTAGG 高阶用法

  • LISTAGG + 开窗函数 OVER PARTITION BY
--执行SQL
WITH temp AS( 
  SELECT 'China' nation ,'Guangzhou' city FROM dual UNION ALL 
  SELECT 'China' nation ,'Shanghai' city FROM dual UNION ALL 
  SELECT 'China' nation ,'Beijing' city FROM dual UNION ALL 
  SELECT 'USA' nation ,'New York' city FROM dual UNION ALL 
  SELECT 'USA' nation ,'Bostom' city FROM dual UNION ALL 
  SELECT 'Japan' nation ,'Tokyo' city FROM dual  
) 
SELECT  
	nation,
	LISTAGG(city,',') WITHIN GROUP (ORDER BY city) OVER (PARTITION BY nation) AS CITIES
FROM temp 
ORDER BY NATION

--返回结果
NATION|CITIES                    |
------|--------------------------|
China |Beijing,Guangzhou,Shanghai|
China |Beijing,Guangzhou,Shanghai|
China |Beijing,Guangzhou,Shanghai|
Japan |Tokyo                     |
USA   |Bostom,New York           |
USA   |Bostom,New York           |


--执行SQL
WITH temp AS( 
  SELECT 'China' nation ,'Guangzhou' city FROM dual UNION ALL 
  SELECT 'China' nation ,'Shanghai' city FROM dual UNION ALL 
  SELECT 'China' nation ,'Beijing' city FROM dual UNION ALL 
  SELECT 'USA' nation ,'New York' city FROM dual UNION ALL 
  SELECT 'USA' nation ,'Bostom' city FROM dual UNION ALL 
  SELECT 'Japan' nation ,'Tokyo' city FROM dual  
) 
SELECT DISTINCT
	nation,
	LISTAGG(city,',') WITHIN GROUP (ORDER BY city) OVER (PARTITION BY nation) AS CITIES
FROM temp 
ORDER BY NATION

--返回结果
NATION|CITIES                    |
------|--------------------------|
China |Beijing,Guangzhou,Shanghai|
Japan |Tokyo                     |
USA   |Bostom,New York           |

参考文章

20/08/21

M

listagg、xmlagg、group_concat()函数用法
Great_est的博客
02-01 1518
listagg、xmlagg、group_concat函数
oracle-listagg()函数实现列转行
bk_huangzongbo的博客
11-22 1885
oracle-listagg()函数实现列转行
Oracle函数之LISTAGG
12-14
近在学习的过程中，发现一个挺有意思的函数，它可实现对列值的拼接。下面我们来看看其具体用法。 　　用法： 　　对其作用，官方文档的解释如下： 　　For a specified measure， LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the measure column. 　　即在每个分组内，LISTAGG根据order by子句对列植进行排序，将排序后的结果拼接起来。 　　measure_expr：可以
oracle利用listagg把多列合成一行并用指定分隔符分割
weixin_39096855的博客
04-22 1461
select listagg(serial_number, ‘’’,’’’) within group (order by serial_number) ||’’’’ from table where rownum<5;
一个关于oracle将多列行合并为单行的函数listagg
lklong123的专栏
08-23 696
select u.user_name,       (        select listagg(g.org_name, '-') within group(order by rownum desc )        from   org g        start  with g.org_id = u.org_id        connect by prior g.porg_id = ...
oralce将多行合并一行(列转行) listagg()函数
a2226701325的博客
03-13 655
有时我们在写sql语句时可能会遇到将多行数据合并成一行的情况，那么这是就可以用这个listagg()函数，listagg()函数是Oracle的列转行函数，但是要注意只有是Oracle11以上版本才支持。 用法： 对其作用，官方文档的解释如下： For a specified measure,LISTAGGorders data within each group s...
oracle wm_concat 列转行 逗号分隔
09-11
LISTAGG函数提供了更好的控制，比如可以指定分隔符，并且是标准的SQL函数，因此在性能和兼容性方面更优。 ```sql SELECT deptID, LISTAGG(username, ', ') WITHIN GROUP (ORDER BY username) AS username, '部门' |...
【oracle】oracle列转行语法糖
weixin_43219644的博客
04-01 328
【代码】oracle列转行语法糖。
小程序云开发中如何生成pdf?
像风一样自由
09-23 5524
需求: 需要将小程序中的界面生成pdf导出 思路: 在云函数中使用nodeJs库是否可以生成？ 小程序端使用canvas生成图片。再转成pdf
oracle里的listagg,oracle中的listagg函数
weixin_42131633的博客
04-09 1万+
listagg()用于字符串聚集LISTAGG(XXX--要合并的列,XXX--分隔符) WITHIN GROUP( ORDER BY XXX) over(partition by XXX)需要注意的事项如下：1.必须得分组，也就是说group by是必须的。2.listagg函数的第一个参数是需要进行合并的字段；第二个参数是分隔符；同时还需要进行排序和分组within group (ord...
【Oracle】Oracle中的LISTAGG函数
最新发布
低调做人，低调编码，神码都是浮云
07-18 1万+
Oracle中的LISTAGG函数-聚合函数
不是单组份组函数错误
gxftry1st的专栏
05-25 1646
Oracle报错：不是单组分组函数解决   报错：不是单组分组函数 实例：select deptno,count(empno) from emp; 报错：不是单组分组函数   原因： 1，  如果程序中使用了分组函数，则有两种情况可以使用：   程序中存在group by，并指定了分组条件，这样可以将分组条件一起查询出来   改为：
SQL多行值合并一行字符串逗号分隔（LISTAGG ）
慢慢
09-25 6464
是一个用于聚合操作的字符串函数，它在Oracle数据库系统中可用，当然达梦也可以。在其他数据库系统中，可能会有类似的函数用于字符串聚合操作，但具体的函数名称、语法和用法可能会有所不同。函数将相同学生的 “成绩” 字段值以逗号分隔的形式合并成一个字符串，并按照 “科目” 字段的顺序进行排序。在查询的SQL使用中我们可能会有这样一种需求：展示学生姓名和科目成绩，科目成绩用逗号分隔。聚合函数是 Oracle 数据库中的特定函数，用于将多个行的值进行连接。假设我们有一张学生表：姓名，科目，成绩。
oracle函数listagg()
再回首恍然如梦
09-30 1万+
在开始演示之前我们需要新建一张表出来： with temp as(     select 'China' nation ,'Guangzhou' city from dual union all     select 'China' nation ,'Shanghai' city from dual union all     select 'China' nation ,
ORA-00937: 不是单组分组函数
李厂长的自留地
08-10 1365
<br />select 列表项中除了包含聚合函数外，还包含了表的某些列，那么你将必须使用group by语句，否则语法通不过。<br /><br />例如：<br />SQL> <br />1 select deptno,job,avg(sal)<br />2 from emp<br />3 ;<br />ERROR 位于第 1 行:<br />ORA-00937: 非单组分组函数<br /> 其实这里也非常容易理解，你既然指定了聚合函数，又同时制定了其他列，还想不按照指定的列来分组，你到底想让ora
ORA-00937:不是单组分组函数_Oracle
weixin_34378922的博客
06-17 1006
Demo: 1 SELECT USER_ID, 2 USER_NAME, 3 USER_SEX, 4 MAX(USER_AGE), 5 SUM(USER_MONEY) AS USER_MONEY 6 USER_TEL, 7 USER_EMAIL, 8 USER_AD...
oracle行转列listagg,用listagg函数分组实现列转行
weixin_31437777的博客
04-03 721
listagg是Oracle 11.2中新增的函数，listagg可以对order by子句中指定的每个组内的数据进行排序，然后连接这些列的值。以下是简单的应用举例：SELECT deptno, listagg(ename, ',') WITHIN GROUP(ORDER BY ename) AS employees FROM scott.emp GROUP BY deptno;可以在livesq...
Oracle列转行函数 Listagg()详解
热门推荐
tianxingyun的专栏
04-28 4万+
详解 listagg()函数可以实现多列记录聚合为一条记录，从而实现数据的压缩、致密化（data densification） 基本用法 像聚合函数一样，通过Group by语句，把每个Group的一个字段，拼接起来 LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX) 示例： with temp as( select '中国' nation ,'江苏' city from dual union all select '中国' nation ,'上海' city
Oracle数据库列转行解决方案
Oracle还提供了其他聚合函数，如`LISTAGG`，它允许你在分组后将列值合并成一个字符串，并且可以指定分隔符。例如： ```sql SELECT a.id, LISTAGG(a.remark, ',') WITHIN GROUP (ORDER BY a.remark) new_result ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值