行逗号连接

 

Oralce sql  行连接  函数 wm_concat(column)

<script></script>标签： oralce sql 行连接 函数 it

分类： Oracle

<!-- 正文开始 -->

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 ;