关于查询结果中多行字段合并到一行上的SQL语句写法

在开发过程中经常碰到要实现按照某一字段，将其它字段中不同的值连接后显示到一行上的需求。本人总结了以下几种方法，在实际开发中可以参考。

建立表结构，在Oracle9i上。
CREATE TABLE temp
(
DEPTNO int,
ENAME varchar(20 BYTE),
SEX varchar(20 BYTE),
AGE varchar(20 BYTE)
);

insert into temp values(10,'aaa','F','18');
insert into temp values(10,'bbb','F','19');
insert into temp values(10,'ccc','F','20');
insert into temp values(20,'ddd','M','21');
insert into temp values(20,'eee','M','22');
insert into temp values(20,'fff','M','23');
insert into temp values(30,'hhh','X','24');
insert into temp values(30,'ggg','X','25');
insert into temp values(30,'yyy','X','26');
insert into temp values(30,'ttt','X','27');

commit;

第一种写法：

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

结果如下：

10| aaa bbb ccc
20| ddd eee fff
30| ggg hhh ttt yyy

改进后可以增加一列如下：

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

结果如下：
10|F| aaa bbb ccc
20|M| ddd eee fff
30|X| ggg hhh ttt yyy

第二种写法：

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

结果如下：
10| aaa bbb ccc|F| 18 19 20
20| ddd eee fff|M| 21 22 23
30| hhh ggg yyy ttt|X| 24 25 26 27