8.1把一列的值合并为一个值,wm_concat和listagg

wm_concat

Select wm_concat(b.name) from a,b where a.id = b.a_id;  --默认是逗号分隔的

把逗号换成其他英文符号

Select replace(wm_concat(b.name),’,’,’|’)  from a,b where a.id = b.a_id;

注意:在PL_SQL中只用wm_concat的话可能查出的是BLOB字段,可以用to_char进行转换一下;

 

Listagg

1.不分区select listagg(c.name, ',') within group( order by c.name) over() as name 

from a,b where a.id = b.a_id;

2.分区select listagg(c.name, ',') within group( order by c.name) over(partition by c.size) as name 

from a,b where a.id = b.a_id;

 

官方文档的例子:https://docs.oracle.com/database/121/SQLRF/functions101.htm

不分区

SELECT LISTAGG(last_name, '; ')

         WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list",

       MIN(hire_date) "Earliest"

  FROM employees

  WHERE department_id = 30;

 

Emp_list                                                     Earliest

------------------------------------------------------------ ---------

Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares            07-DEC-02

分区:

SELECT department_id "Dept", hire_date "Date", last_name "Name",

       LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name)

         OVER (PARTITION BY department_id) as "Emp_list"

  FROM employees

  WHERE hire_date < '01-SEP-2003'

  ORDER BY "Dept", "Date", "Name";

 

 Dept Date      Name            Emp_list

----- --------- --------------- ---------------------------------------------

   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