查询结果中多行字段合并到一行上的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