Oracle中 列转行、多行变单行函数 listagg()

在工作中偶尔需要将多行数据转换成一行显示,由于不经常使用,故此处记录一下方便下次查看

 listagg(expr1,expr2) within group(order by expr3)

expr1:需要进行转换的列,可以单列也可以是多列
expr2:列与列之间的分隔符,比如逗号(,)、分号(;)等等
expr3:排序列

接下来官网的示例说明

Oracle 官网关于listagg函数的说明

需求一:汇总列出了hr.employees表中部门ID=30中的所有员工,并按雇用日期和姓名排序:
SELECT LISTAGG(last_name, '; ')
         WITHIN GROUP (ORDER BY hire_date, last_name) "所有员工",
       MIN(hire_date) "最小雇佣日期"
  FROM employees
  WHERE department_id = 30;

所有员工                                                      最小雇佣日期
------------------------------------------------------------ ---------
Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares            2020-02-25
需求二:以组集合汇总示例针对hr.employees表中的每个部门ID ,按其雇用日期的顺序列出了该部门中的员工:
SELECT department_id "部门.",
       LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date) "部门下的所有员工"
  FROM employees
  GROUP BY department_id
  ORDER BY department_id;

部门.   部门下的所有员工
------ ------------------------------------------------------------
    10 Whalen
    20 Hartstein; Fay
    30 Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares
    40 Mavris
    50 Kaufling; Ladwig; Rajs; Sarchand; Bell; Mallin; Weiss; Davie
       s; Marlow; Bull; Everett; Fripp; Chung; Nayer; Dilly; Bissot
       ; Vollman; Stiles; Atkinson; Taylor; Seo; Fleaur; Matos; Pat
       el; Walsh; Feeney; Dellinger; McCain; Vargas; Gates; Rogers;
        Mikkilineni; Landry; Cabrio; Jones; Olson; OConnell; Sulliv
       an; Mourgos; Gee; Perkins; Grant; Geoni; Philtanker; Markle
    60 Austin; Hunold; Pataballa; Lorentz; Ernst
    70 Baer
. . .
需求三:以下示例与上一示例相同,不同之处在于它包含该ON OVERFLOW TRUNCATE子句。出于本示例的目的,假定返回值的最大长度是人为的200字节的小数字。由于部门50的员工列表超过200个字节,因此该列表被截断并附加了最终定界符’ ;’,指定的截断指示符’ …‘和截断值的数量’ (23)’。
SELECT department_id "部门.",
       LISTAGG(last_name, '; ' ON OVERFLOW TRUNCATE '...')
               WITHIN GROUP (ORDER BY hire_date) "部门下的所有员工"
  FROM employees
  GROUP BY department_id
  ORDER BY department_id;

部门.   部门下的所有员工
------ ------------------------------------------------------------
    10 Whalen
    20 Hartstein; Fay
    30 Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares
    40 Mavris
    50 Kaufling; Ladwig; Rajs; Sarchand; Bell; Mallin; Weiss; Davie
       s; Marlow; Bull; Everett; Fripp; Chung; Nayer; Dilly; Bissot
       ; Vollman; Stiles; Atkinson; Taylor; Seo; Fleaur; ... (23)
    70 Baer
. . .
需求四:分析示例,对于2003年9月1日之前雇用的每个员工,该员工的部门,雇用日期以及该部门中的所有其他员工也在2003年9月1日之前雇用
SELECT department_id "部门", hire_date "雇佣日期", last_name "员工",
       LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name)
         OVER (PARTITION BY department_id) as "其他员工"
  FROM employees
  WHERE hire_date < '01-SEP-2003'
  ORDER BY "Dept", "Date", "Name";

 部门  雇佣日期      员工           其他员工
----- --------- --------------- ---------------------------------------------
   30 07-DEC-02 Raphaely        Raphaely; Khoo
   30 18-MAY-03 Khoo            Raphaely; Khoo
   40 07-JUN-02 Mavris          Mavris
   50 01-MAY-03 Kaufling        Kaufling; Ladwig
   50 14-JUL-03 Ladwig          Kaufling; Ladwig
   70 07-JUN-02 Baer            Baer
   90 13-JAN-01 De Haan         De Haan; King
   90 17-JUN-03 King            De Haan; King
  100 16-AUG-02 Faviet          Faviet; Greenberg
  100 17-AUG-02 Greenberg       Faviet; Greenberg
  110 07-JUN-02 Gietz           Gietz; Higgins
  110 07-JUN-02 Higgins         Gietz; Higgins
Oracle中 列转行、多行变单行函数 listagg()Oracle中 列转行、多行变单行函数 listagg() 乐享生活522 发布了2 篇原创文章 · 获赞 0 · 访问量 15 私信 关注
上一篇:3. JPA对象关系映射 -- 级联操作


下一篇:mysql——多表——外连接查询——左连接、右连接、复合条件查询