Oracle行转列函数listagg,多行转一列

listagg

用法 listagg( 需要合并的数据列(可以使用 || 合并一行多列) ,‘合并中需要的分隔符号’)

–例如 listagg( name || ‘.’ || gender , ‘,’)

扩展用法 ：分组排序后合并

(listagg( 需要合并的数据列 ,‘合并中需要的分隔符号’)within group(order by 排序)

–例如 listagg( name || ‘.’ || gender ,‘,’) within group(order by serial_no)

–对比 wm_concat() OVER (PARTITION BY serial_no order BY serial_no)不会因为合并行数生成对应的行数，如合并五行出现五行合并后数据。

---

LISTAGG Function（From Oracle Help File）

The LISTAGG function orders data within each group based on the ORDER BY clause and then concatenates the values of the measure column. Its syntax is as follows:

LISTAGG ( [, ) WITHIN GROUP (ORDER BY <oby_expression_list>)
expr can be a column, constant, bind variable, or an expression involving them.

delimiter can be a column, constant, bind variable, or an expression involving them.

oby_expression_list can be a list of expressions with optional ordering options to sort in ascending or descending order (ASC or DESC), and to control the sort order of NULLs (NULLS FIRST or NULLS LAST). ASCENDING and NULLS LAST are the defaults.

LISTAGG as Aggregate
You can use the LISTAGG function as an aggregate.

Example 22-18 LISTAGG as Aggregate

The following example illustrates using LISTAGG as an aggregate.

SELECT prod_id, LISTAGG(cust_first_name||' '||cust_last_name, '; ') 
  WITHIN GROUP (ORDER BY amount_sold DESC) cust_list
FROM sales, customers
WHERE sales.cust_id = customers.cust_id AND cust_gender = 'M' 
  AND cust_credit_limit = 15000 AND prod_id BETWEEN 15 AND 18 
  AND channel_id = 2 AND time_id > '01-JAN-01'
GROUP BY prod_id;

PROD_ID  CUST_LIST
-------  -----------------------------------------------
     15  Hope Haarper; Roxanne Crocker; ... Mason Murray
     16  Manvil Austin; Bud Pinkston; ... Helga Nickols
     17  Opal Aaron; Thacher Rudder; ... Roxanne Crocker
     18  Boyd Lin; Bud Pinkston; ... Erik Ready
The output has been modified for readability.

LISTAGG as Reporting Aggregate
You can use the LISTAGG function as a reporting aggregate.

Example 22-19 LISTAGG as Reporting Aggregate

This example illustrates using LISTAGG as a reporting aggregate:

connect sh/sh
set lines 120 pages 20000
column list format A40

SELECT time_id, prod_id, MIN(amount_sold), LISTAGG(min(amount_sold),';') 
WITHIN GROUP (ORDER BY prod_id) OVER (PARTITION BY time_id) cust_list 
FROM sales WHERE time_id > '20-DEC-01' AND prod_id BETWEEN 120 AND 125 
GROUP BY prod_id, time_id;

TIME_ID   PROD_ID   MIN(AMOUNT_SOLD)  CUST_LIST
-------   -------   ----------------  -----------
21-DEC-01     120            51.36    51.36;10.81
21-DEC-01     121            10.81    51.36;10.81
22-DEC-01     120            51.36    51.36;10.81;20.23;56.12;17.79;15.67
22-DEC-01     121            10.81    51.36;10.81;20.23;56.12;17.79;15.67
22-DEC-01     122            20.23    51.36;10.81;20.23;56.12;17.79;15.67
22-DEC-01     123            56.12    51.36;10.81;20.23;56.12;17.79;15.67
22-DEC-01     124            17.79    51.36;10.81;20.23;56.12;17.79;15.67
22-DEC-01     125            15.67    51.36;10.81;20.23;56.12;17.79;15.67

---- end ----