code name emp
-----------------
01 风扇 张三
01 风扇 李四
02 电视 张三
02 电视 李四
02 电视 王五写一条select成如下效果:
cod nam emp
----------------
01 风扇 张三,李四
02 电视 张三,李四,王五
-----------------
01 风扇 张三
01 风扇 李四
02 电视 张三
02 电视 李四
02 电视 王五写一条select成如下效果:
cod nam emp
----------------
01 风扇 张三,李四
02 电视 张三,李四,王五
select code,name,wm_concat(emp) as emp
from t
group by code,name;
FROM 表名
GROUP BY CODE, NAME;
select code,name,substr(max(sys_connect_by_path(emp,',')),2) emp
from (select a.*,row_number()over(partition by code,name order by emp) rn from sys_region a )
group by code,name
start with rn=1
connect by rn-1=prior rn and code=prior code and name=prior name
order by 1
with tb_name as
(
select '01' as code,'风扇' as name,'张三' as emp from dual
union all
select '01' as code,'风扇' as name,'李四' as emp from dual
union all
select '02' as code,'电视' as name,'王五' as emp from dual
union all
select '02' as code,'电视' as name,'李四' as emp from dual
union all
select '02' as code,'电视' as name,'王五' as emp from dual
)
select code,name,substr(max(sys_connect_by_path(emp,',')),2) emp
from (select a.*,row_number()over(partition by code,name order by emp) rn from tb_name a )
group by code,name
start with rn=1
connect by rn-1=prior rn and code=prior code and name=prior name
order by 1