oracle 列合并行,Oracle SQL 将同一列多行合并到一列中，行列合并

箱号

1

2

3

4

5

合并成     ,1,2,3,4,5

create or replace function get_carton_list ( p_pkt_ctrl_nbr in carton_hdr.pkt_ctrl_nbr%type, p_sku_id in carton_hdr.sku_id%type, p_carton_creation_code in carton_hdr.carton_creation_code%type ) return varchar as v_carton_list varchar2(2000); begin select text into v_carton_list from ( select row_number()over(partition by groupname order by groupname,lvl desc) rn,groupname,text from ( select a.groupname,level lvl,SYS_CONNECT_BY_PATH(a.carton_nbr_x_of_y,',') text from (   select a.pkt_ctrl_nbr||'-'||a.sku_id||'-'||a.carton_creation_code groupname,   a.carton_nbr_x_of_y,row_number() over(partition by a.pkt_ctrl_nbr||'-'||a.sku_id||'-'||a.carton_creation_code order by a.carton_nbr_x_of_y) x   from (select distinct a.pkt_ctrl_nbr,b.sku_id,case when a.carton_creation_code in (5,26) then '拼箱' else '整箱' end carton_creation_code,a.carton_nbr_x_of_y from carton_hdr a inner join carton_dtl b on a.carton_nbr=b.carton_nbr   where 1=1 and a.stat_code='90' and a.pkt_ctrl_nbr=p_pkt_ctrl_nbr and b.sku_id=p_sku_id    and (case when a.carton_creation_code in (5,26) then '拼箱' else '整箱' end)=(case when p_carton_creation_code in (5,26) then '拼箱' else '整箱' end)   ) a ) a connect by a.groupname=prior a.groupname and x-1=prior x ) t ) t where rn=1; return v_carton_list; exception   when others then     return null; end;