在Oracle數(shù)據(jù)庫中,LISTAGG函數(shù)用于將多行數(shù)據(jù)合并為單個(gè)字符串。
下面是LISTAGG函數(shù)的基本語法:
LISTAGG(column_name, delimiter) WITHIN GROUP (ORDER BY column_name)
其中:
column_name
是要合并的列名。delimiter
是用于分隔合并結(jié)果的字符串。ORDER BY
子句是可選的,用于指定合并結(jié)果的排序順序。例如,假設(shè)有一個(gè)表employee
包含以下數(shù)據(jù):
| employee_id | employee_name |
|-------------|---------------|
| 1 | John |
| 2 | Amy |
| 3 | Bob |
可以使用LISTAGG函數(shù)將employee_name
列的值合并為一個(gè)字符串,并用逗號(hào)分隔:
SELECT LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_id) AS employees
FROM employee;
執(zhí)行以上查詢將返回以下結(jié)果:
employees
---------------------
John, Amy, Bob