多行合一 orcale

[sql]代码库

CREATE TABLE dept(DEPTID int,ENAME varchar(20 BYTE),SEX varchar(20 BYTE),AGE varchar(20 BYTE));
insert into dept values(1,'张三','F','18'); 
insert into dept values(1,'李四','F','19'); 
insert into dept values(1,'王五','F','20'); 
insert into dept values(2,'刘六','M','21'); 
insert into dept values(2,'赵七','M','22'); 
insert into dept values(2,'曹九','M','23');

commit;
select * from dept;
SELECT DISTINCT FIRST_VALUE(deptid) OVER(PARTITION BY deptid ORDER BY lv DESC) AS deptid,
FIRST_VALUE(ename) OVER(PARTITION BY deptid ORDER BY lv DESC) AS ename
FROM (SELECT deptid, SYS_CONNECT_BY_PATH(ename, ' ') ename, LEVEL lv
FROM (SELECT deptid,ename,LAG(ename, 1, NULL) OVER(PARTITION BY deptid ORDER BY ename) ename_1
FROM (SELECT deptid, ename FROM dept))
CONNECT BY PRIOR ename = ename_1
ORDER BY deptid, ename);

SELECT DISTINCT FIRST_VALUE (deptid) OVER (PARTITION BY deptid ORDER BY lv DESC) AS deptid, 
FIRST_VALUE (sex) OVER (PARTITION BY sex ORDER BY lv DESC) AS sex, 
FIRST_VALUE (ename) OVER (PARTITION BY deptid ORDER BY lv DESC) AS ename 
FROM (SELECT deptid, sex, SYS_CONNECT_BY_PATH (ename, ' ') ename, LEVEL lv 
FROM (SELECT deptid, ename, sex, 
LAG (ename, 1, NULL) OVER (PARTITION BY deptid ORDER BY ename) ename_1 
FROM (SELECT deptid, ename, sex FROM dept)) 
CONNECT BY PRIOR ename = ename_1 
ORDER BY deptid) ;

SELECT deptid,SYS_CONNECT_BY_PATH(ename, ' ') AS ename,sex,SYS_CONNECT_BY_PATH(age, ' ') AS age
FROM (SELECT deptid,ename,sex,age,
RANK() OVER(ORDER BY deptid)+ROW_NUMBER() OVER(ORDER BY deptid) rn,
ROW_NUMBER() OVER(PARTITION BY deptid ORDER BY deptid) rm
FROM dept) a1
WHERE a1.ROWID IN (SELECT MAX(a2.ROWID) FROM dept a2 WHERE a2.deptid = a1.deptid)
START WITH rm = 1
CONNECT BY PRIOR rn = rn - 1;