Oracle合并多行为一行|极客笔记

Oracle合并多行为一行

在Oracle数据库中，有时候我们需要将多行数据合并为一行，这在处理一些复杂查询结果时特别有用。本文将详细介绍如何使用Oracle SQL语句将多行数据合并为一行。

案例介绍

假设我们有一个employees表，包含了员工的姓名和部门信息，现在我们需要将相同部门的员工姓名合并为一行，部门信息只显示一次。

create table employees(
    id number,
    name varchar2(50),
    department varchar2(50)
);

insert into employees values(1, 'Alice', 'HR');
insert into employees values(2, 'Bob', 'HR');
insert into employees values(3, 'Charlie', 'IT');
insert into employees values(4, 'David', 'IT');

现在employees表的数据如下所示：

| ID |  NAME   | DEPARTMENT |
|----|---------|------------|
| 1  | Alice   | HR         |
| 2  | Bob     | HR         |
| 3  | Charlie | IT         |
| 4  | David   | IT         |

我们希望查询结果合并为以下形式：

| DEPARTMENT |               EMPLOYEES               |
|------------|---------------------------------------|
| HR         | Alice, Bob                           |
| IT         | Charlie, David                       |

使用`LISTAGG`函数

Oracle提供了LISTAGG函数来实现将多行数据合并为一行的功能。LISTAGG函数用法如下：

LISTAGG(column, separator) WITHIN GROUP (ORDER BY order_column)

column为需要合并的列名；
separator为分隔符；
order_column为排序列，可选。

对于上面的案例，我们可以使用如下SQL语句实现需求：

SELECT department, LISTAGG(name, ', ') WITHIN GROUP (ORDER BY id) AS employees
FROM employees
GROUP BY department;

运行以上SQL语句，得到合并后的查询结果：

| DEPARTMENT |          EMPLOYEES           |
|------------|------------------------------|
| HR         | Alice, Bob                   |
| IT         | Charlie, David               |

使用子查询和`LISTAGG`函数

在某些情况下，我们可能需要在合并的同时显示部门的其他信息。此时，我们可以使用子查询和LISTAGG函数结合的方式来实现。

假设departments表包含了部门的其他信息：

create table departments(
    department_name varchar2(50),
    location varchar2(50)
);

insert into departments values('HR', 'New York');
insert into departments values('IT', 'San Francisco');

我们可以使用以下SQL语句实现合并后同时显示部门信息：

SELECT d.department_name, d.location, e.employees
FROM (
    SELECT department, LISTAGG(name, ', ') WITHIN GROUP (ORDER BY id) AS employees
    FROM employees
    GROUP BY department
) e
JOIN departments d
ON e.department = d.department_name;

运行以上SQL语句，得到合并后的查询结果：

| DEPARTMENT_NAME | LOCATION       | EMPLOYEES           |
|-----------------|----------------|---------------------|
| HR              | New York       | Alice, Bob          |
| IT              | San Francisco  | Charlie, David      |