Oracle分组查询用逗号分隔结果SQL语句(2)

Oracle分组查询用逗号分隔结果SQL语句(2)

 

问题
有一张A表，里面有两个字段  
字段1   字段2      
 A           500       
 B           300       
 B           400       
 B           600       
 A           200
   最后结果为    B     300,400,600   后面的值为拼装成带逗号。
作答：
create table a_lyh_test
as
select  'A' as "字段1" , 500 as "字段2" from dual
union all
select  'B' as "字段1" , 300 as "字段2" from dual
union all
select  'B' as "字段1" ,400 as "字段2" from dual
union all
select  'B' as "字段1" , 600 as "字段2" from dual
union all
select  'A' as "字段1" , 200 as "字段2" from dual
;

select f.字段1
      ,ltrim(max(sys_connect_by_path(f.字段2,','))
      keep (dense_rank last order by f.pnum),',') as 字段2
from
(
    select t.字段1
         ,t.字段2
         ,row_number() over(partition by t.字段1  order by t.字段1) as pnum
         ,row_number() over(partition by t.字段1  order by t.字段1)-1 as lnum
    from
    (
    select a.字段1,a.字段2
    from a_lyh_test   a
    ) t
) f
group by f.字段1
connect by f.lnum = prior f.pnum and f.字段1 = prior f.字段1
start with f.pnum = 1;

 

-- 刘轶鹤