Oralce sql 行连接 函数 wm_concat(column)
<!-- 正文开始 -->
1、数据库表 test 如下
id , name ,sex
1 jia 1
2 yang 2
3 xiao 2
想要得到的结果是 男女性别的 name 连接在一起 如:
jia
yang xiao
select wm_concat(name) from test group by sex;
1,jia
2,yang,xiao
ID,NAME,DATE,
根据ID分组,date排序,得到name的顺序逗号隔开
with temp as(
select 1 id,'haha88' name,to_date('2013-02-03','yyyy-MM-dd') mydate from dual
union all
select 1 id,'haha99' name,to_date('2013-02-04','yyyy-MM-dd') mydate from dual
union all
select 2 id,'haha77' name,to_date('2013-02-03','yyyy-MM-dd') mydate from dual
union all
select 3 id,'haha1' name,to_date('2013-02-01','yyyy-MM-dd') mydate from dual
union all
select 3 id,'haha2' name,to_date('2013-02-03','yyyy-MM-dd') mydate from dual
union all
select 3 id,'haha3' name,to_date('2013-02-02','yyyy-MM-dd') mydate from dual
union all
select 3 id,'haha4' name,to_date('2013-02-04','yyyy-MM-dd') mydate from dual)
select id, max(r)
from (select id, wm_concat(name) over (partition by id order by mydate) r from temp)
group by id ;