oracle10g，9i多行合并一行函数

### Oracle 10g与9i中的多行合并为一行函数详解 在Oracle数据库系统中，经常需要将多个行的数据合并成单行显示，尤其是在处理报告或者需要将多个值组合成一个字符串的情况下。本文将详细介绍如何在Oracle 10g与9i版本中实现这一功能，并分别提供具体的实现方法。 #### 一、Oracle 10g 多行合并为一行函数 在Oracle 10g中，可以通过`wmsys.wm_concat()`函数来轻松地将多行数据合并到一行中。此函数可以接受多个输入参数，并返回一个由所有参数组成的字符串列表。默认情况下，这些值会用逗号`,`分隔。如果需要使用其他字符作为分隔符，则可以通过`replace()`函数进行替换。 **示例代码：** ```sql SELECT name, REPLACE(wmsys.wm_concat(course), ',', ';') AS courses FROM table_name GROUP BY name; ``` 在这个例子中： 1. **`wmsys.wm_concat(course)`**：使用`wm_concat()`函数将`course`列中的所有值合并为一个字符串列表。 2. **`REPLACE(wmsys.wm_concat(course), ',', ';')`**：通过`replace()`函数将逗号`,`替换为分号`;`，从而改变默认的分隔符。 3. **`GROUP BY name`**：按照`name`列进行分组，确保每个名字对应的课程列表只出现一次。 #### 二、Oracle 9i 多行合并为一行函数 在Oracle 9i中，由于`wm_concat()`函数尚未被引入，因此需要采用更复杂的方法来实现多行合并为一行的功能。这里介绍一种使用`sys_connect_by_path()`函数结合`connect by`语法的方法。 **示例代码：** ```sql SELECT name, MAX(REPLACE(SUBSTR(sys_connect_by_path(course, '*'), 2), '*', ';')) AS courses FROM ( SELECT name, course, DENSE_RANK() OVER (ORDER BY name) + ROW_NUMBER() OVER (ORDER BY name) rid, ROW_NUMBER() OVER (PARTITION BY name ORDER BY name) nid FROM table_name ) START WITH nid = 1 CONNECT BY PRIOR rid = rid - 1 GROUP BY name; ``` 这个查询的执行过程如下： 1. **子查询部分**： - 使用`DENSE_RANK()`和`ROW_NUMBER()`函数为每一行分配一个编号（`rid`和`nid`），便于之后使用`connect by`语法连接这些行。 - `DENSE_RANK() OVER (ORDER BY name) + ROW_NUMBER() OVER (ORDER BY name)`计算每个记录的唯一ID。 - `ROW_NUMBER() OVER (PARTITION BY name ORDER BY name)`用于确定每个名字的第一行。 2. **主查询部分**： - 使用`sys_connect_by_path()`函数将`course`列中的所有值连接起来，默认分隔符为`*`。 - 通过`SUBSTR(sys_connect_by_path(course, '*'), 2)`去除第一个星号。 - 使用`REPLACE()`函数将剩余的星号替换为分号`;`。 - 使用`MAX()`函数获取每个名字下的连接结果，因为对于每个名字，连接后的结果是相同的。 通过上述两个示例，我们可以看到，在Oracle的不同版本中，虽然实现方式有所不同，但都能有效地将多行数据合并为一行。这两种方法各有特点，可以根据实际使用的Oracle版本和个人偏好选择合适的方式。