很多情况下会遇到这种情况:
表里的数据是:
id name
3200 01
3200 02
3200 03
3200 04
需要的结果是:
id name
3200 01,02,03,04
其实现脚本是:
1 、在10g下:
with t as (
select 3200 id , 01 name from dual
union all
select 3200 id , 02 name from dual
union all
select 3200 id , 03 name from dual
union all
select 3200 id , 04 name from dual
)
select id,wmsys.wm_concat(name) name from t group by id;
2、在9i下:
select * from t
select id, substr(max(sys_connect_by_path( name,',')),2) name
from (select t.*,row_number()over(order by name) rn from t )
group by id
start with rn=1
connect by rn-1=prior rn