[转]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

相关文章