Oracle多行合并成一行以分号分隔|极客笔记

Oracle多行合并成一行以分号分隔

在Oracle数据库中，有时候我们需要将多行数据合并成一行，并用分号进行分隔。本文将详细介绍如何在Oracle中实现这种功能。

1. 问题背景与需求

假设我们有一张名为employees的表，包含以下字段：

emp_id（员工ID）
emp_name（员工姓名）
emp_department（员工部门）

我们的需求是将同一个部门的所有员工姓名合并成一行，并用分号进行分隔。例如，假设表中有以下数据：

emp_id	emp_name	emp_department
1	Alex	HR
2	Bob	HR
3	Carol	IT
4	Dave	IT

我们希望得到以下结果：

emp_department	emp_names
HR	Alex; Bob
IT	Carol; Dave

2. 解决方案

2.1 使用LISTAGG函数

在Oracle 11g及以后的版本中，可以使用LISTAGG函数实现将多行数据合并成一行，并用指定的分隔符进行分隔。LISTAGG函数的语法如下：

LISTAGG(column, delimiter) WITHIN GROUP (ORDER BY column)

其中，column表示要合并的列名称，delimiter表示分隔符。WITHIN GROUP子句用于指定合并时的排序规则。

根据我们的需求，我们可以使用以下SQL语句来实现：

SELECT emp_department, LISTAGG(emp_name, '; ') WITHIN GROUP (ORDER BY emp_id) AS emp_names
FROM employees
GROUP BY emp_department;

执行上述SQL语句后，将得到以下结果：

emp_department	emp_names
HR	Alex; Bob
IT	Carol; Dave

2.2 使用XMLAGG函数和XMLELEMENT函数

在Oracle中，我们还可以使用XMLAGG函数和XMLELEMENT函数来将多行数据合并成一行，并用指定的分隔符进行分隔。

首先，我们使用XMLELEMENT函数来将每一行的员工姓名封装为一个XML元素。然后，我们使用XMLAGG函数将这些XML元素进行聚合。最后，我们使用EXTRACT函数从聚合的XML中提取出合并后的文本。

具体实现如下：

SELECT emp_department, RTRIM(EXTRACT(XMLAGG(XMLELEMENT(e, emp_name || '; ')), '/e/text()').getStringVal(), '; ') AS emp_names
FROM employees
GROUP BY emp_department;

执行上述SQL语句后，将得到以下结果：

emp_department	emp_names
HR	Alex; Bob
IT	Carol; Dave

3. 示例代码运行结果

下面是在Oracle数据库中使用示例数据执行上述两种解决方案的结果：

-- 创建测试表employees并插入示例数据
CREATE TABLE employees (
  emp_id NUMBER,
  emp_name VARCHAR2(20),
  emp_department VARCHAR2(20)
);

INSERT INTO employees VALUES (1, 'Alex', 'HR');
INSERT INTO employees VALUES (2, 'Bob', 'HR');
INSERT INTO employees VALUES (3, 'Carol', 'IT');
INSERT INTO employees VALUES (4, 'Dave', 'IT');

-- 使用LISTAGG函数合并多行数据
SELECT emp_department, LISTAGG(emp_name, '; ') WITHIN GROUP (ORDER BY emp_id) AS emp_names
FROM employees
GROUP BY emp_department;

-- 使用XMLAGG函数和XMLELEMENT函数合并多行数据
SELECT emp_department, RTRIM(EXTRACT(XMLAGG(XMLELEMENT(e, emp_name || '; ')), '/e/text()').getStringVal(), '; ') AS emp_names
FROM employees
GROUP BY emp_department;

运行上述SQL代码后，将分别得到以下结果：

-- 使用LISTAGG函数合并多行数据的结果
emp_department  |  emp_names
HR              |  Alex; Bob
IT              |  Carol; Dave

-- 使用XMLAGG函数和XMLELEMENT函数合并多行数据的结果
emp_department  |  emp_names
HR              |  Alex; Bob
IT              |  Carol; Dave