oracle分析函数listagg

drop table t_thz_listagg;
create table t_thz_listagg(
n_id number(8),
s_name varchar2(100),
n_deptno number(8)
);
insert into t_thz_listagg(n_id,s_name,n_deptno)
select 1 ,‘麦片‘,20 from dual
union all
select 2 ,‘西瓜‘,30 from dual
union all
select 3 ,‘芝麻糊‘,20 from dual
union all
select 4 ,‘保温杯‘,10 from dual
union all
select 5 ,‘哈密瓜‘,30 from dual
union all
select 6 ,‘燕麦‘,20 from dual
union all
select 7 ,‘‘,40 from dual
union all
select 8 ,‘‘,40 from dual
;
commit;
select * from t_thz_listagg;

--分组

select n_deptno
,listagg(s_name,‘,‘) within group (order by n_id) as str
from t_thz_listagg
group by n_deptno;

--不分组

select t.*,listagg(s_name,‘,‘) within group (order by n_id) over (partition by n_deptno) from t_thz_listagg t;