[转]Oracle 多行的数据合并

本文转自：http://www.2cto.com/database/201203/125287.html

 

Oracle合并行范例

 

现有如下数据

id name

1 a1

2 a2

3 a3

1 b1

3 b3

1 c1

 

就是把相同的id的不同行合并

结果是

id name

1 a1/b1/c1

2 a2

3 a3/b3

 

实现：适用8i以后的:

 

Sql代码 

--适用8i以后的  www.2cto.com SELECT t.id id, MAX(substr(sys_connect_by_path(t.name, '/'), 2)) str 

  FROM (SELECT id, name, row_number() over(PARTITION BY id ORDER BY name) rn 

  FROM TABLE_NAME) tSTART WITH rn = 1CONNECT BY rn = PRIOR rn + 1 AND id = PRIOR idGROUP BY t.id; 

 

---适用于10g以后 : 

 

SELECT ID, REPLACE(WMSYS.WM_CONCAT(NAME), ',', '/') "NEW_NAME" 

  FROM TABLE_NAME TT 

GROUP BY ID; 

 

 

 

 

作者 wm920