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以后的:

 

--适用8i以后的
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;