oracle sql 行值合并,Oracle PLSQL 行合并

select created_by, translate(ltrim(text, '/'), '*/', '*,') researcherlist

from (select row_number() over(partition by created_by order by created_by, lvl desc) rn,

created_by,

text

from (select created_by,

level lvl,

sys_connect_by_path(c_researcher_code, '/') text

from (select created_by,

user_name as c_researcher_code,

row_number() over(partition by created_by order by created_by, user_name) x

from fnd_user

order by created_by, user_name) a

connect by created_by = prior created_by

and x – 1 = prior x))

where rn = 1

order by created_by;

SQL分析：

1、利用 “ROW_NUMBER () OVER (PARTITION BY……” 为按“创建人”汇总后的数据行添加组内序号

2、“SYS_CONNECT_BY_PATH”  按组内序号相邻关系，为每一层进行不同行的“名称”叠加

3、再次利用“创建人”进行组内分组，但按第二部中的层次排倒序，增加调整后等级

4、取所有调整后等级为1的结果，即为所要求的数据行