Oracle多行合并成一行用逗号隔开

Oracle多行合并成一行用逗号隔开

1. 前言

在实际数据库操作中，我们经常会遇到将多行数据合并成一行的需求。在Oracle数据库中，我们可以使用聚合函数CONCAT和LISTAGG来实现该功能。本文将详细介绍在Oracle中将多行数据合并成一行，并使用逗号作为分隔符的方法。

2. CONCAT函数

CONCAT函数用于连接两个字符串。

2.1 语法格式

CONCAT(string1, string2)

2.2 示例

假设我们有一个表employee，包含以下数据：

我们可以使用CONCAT函数将emp_name连接成一个字符串，代码如下：

SELECT CONCAT(emp_name, ',') FROM employee;

输出为：

Alice,
Bob,
Charlie,

3. LISTAGG函数

LISTAGG函数用于将多行数据合并成一行，并带有自定义的分隔符。

3.1 语法格式

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

• expr：要连接的列或表达式。
• delimiter：指定的分隔符。可以为任意字符串。
• column：用于排序的列。可选项。

3.2 示例

假设我们有一个表fruit，包含以下数据：

我们可以使用LISTAGG函数将fruit_name合并成一行，代码如下：

SELECT LISTAGG(fruit_name, ',') WITHIN GROUP (ORDER BY fruit_id) FROM fruit;

输出为：

Apple,Banana,Orange

4. 使用子查询和LISTAGG函数合并多行成一行

有时候我们需要根据不同的条件对数据进行分组，并将每个分组的数据合并成一行。这时我们可以使用子查询和LISTAGG函数的组合。

4.1 示例

假设我们有一个表score，包含以下数据：

我们可以使用子查询和LISTAGG函数将每个学生的科目和成绩合并成一行，代码如下：

SELECT student_id, LISTAGG(subject || ':' || score, ',') WITHIN GROUP (ORDER BY subject) AS result
FROM
    (SELECT student_id, subject, score FROM score)
GROUP BY student_id;

输出为：

| student_id | result                   |
|------------|--------------------------|
| 1          | Math:80,English:90       |
| 2          | English:75,Math:85,Science:90 |
| 3          | Math:95,Science:85       |

5. 使用XMLAGG函数合并成XML格式

除了使用逗号分隔，我们还可以使用XMLAGG函数将多行数据合并成XML格式。

5.1 示例

假设我们有一个表city，包含以下数据：

我们可以使用XMLAGG函数将city_name合并成XML格式，代码如下：

SELECT XMLELEMENT("cities", XMLAGG(XMLELEMENT("city", city_name))) AS result FROM city;

输出为：

<cities>
  <city>Beijing</city>
  <city>Shanghai</city>
  <city>Hangzhou</city>
</cities>

6. 总结

本文介绍了在Oracle数据库中将多行数据合并成一行，并使用逗号作为分隔符的方法。我们可以使用CONCAT函数对两个字符串进行连接，也可以使用LISTAGG函数将多行数据合并成一行。同时，我们还介绍了使用子查询和LISTAGG函数的组合，以及使用XMLAGG函数将多行数据合并成XML格式。