分组查询将多行字段合并到一行的SQL写法，大家看看该怎么写~~~

Question

想使用SYS_CONNECT_BY_PATH 来实现如下效果CREATE TABLE temp 
( 
DEPTNO   int, 
ENAME   varchar(20 BYTE), 
SEX   varchar(20 BYTE), 
AGE   varchar(20 BYTE) ,
BIRTHDAY DATE
);  insert into temp values(10,'aaa','F','18',TO_DATE('2011-09-28','yyyy-mm-dd'));  
insert into temp values(10,'bbb','F','19',TO_DATE('2011-09-29','yyyy-mm-dd'));  
insert into temp values(10,'ccc','M','20',TO_DATE('2011-09-28','yyyy-mm-dd'));  
insert into temp values(20,'ddd','M','21',TO_DATE('2011-09-28','yyyy-mm-dd'));    
insert into temp values(20,'eee','M','22',TO_DATE('2011-09-28','yyyy-mm-dd'));    
insert into temp values(20,'fff','M','23',TO_DATE('2011-09-28','yyyy-mm-dd'));    
insert into temp values(30,'hhh','X','24',TO_DATE('2011-09-28','yyyy-mm-dd'));    
insert into temp values(30,'ggg','X','25',TO_DATE('2011-09-28','yyyy-mm-dd'));    
insert into temp values(30,'yyy','X','26',TO_DATE('2011-09-28','yyyy-mm-dd'));    
insert into temp values(30,'ttt','X','27',TO_DATE('2011-09-28','yyyy-mm-dd'));    想达到的效果如下：按部门、性别、生日三个分组，这个组中的所有名字，在一行显示出来。
如下所示：
10   F    2011-09-28    aaa
10   F    2011-09-29    bbb
10   M    2011-09-28   ccc
20   M    2011-09-28   ddd,eee,fff
30   X    2011-09-28   hhh,ggg,yyy,tttSELECT deptno, sex, birthday,SYS_CONNECT_BY_PATH (ename, ',') AS ename, 
FROM (SELECT deptno, ename, sex, age, RANK () OVER (ORDER BY deptno) 
                   + ROW_NUMBER () OVER (ORDER BY deptno) rn, 
                   ROW_NUMBER () OVER (PARTITION BY deptno,sex,birthday ORDER BY deptno) rm 
              FROM temp) a1 
     WHERE a1.ROWID IN (SELECT MAX (a2.ROWID) FROM temp a2 WHERE a2.deptno = a1.deptno
a2.birthday=a1.birthday and a2.sex=a1.sex) 
START WITH rm = 1 
CONNECT BY PRIOR rn = rn - 1 
但是上面的语句达不到我的效果，大家帮忙看看，哪里出错了。多谢多谢~~~

Answer 1

不知道什么结果，如果有错再改改
SELECT deptno, 
       sex, 
       birthday,
       SYS_CONNECT_BY_PATH (ename, ',') AS ename,  
  FROM 
       ( 
         SELECT deptno, 
                ename, 
                sex, 
                age,
                ROW_NUMBER () OVER (partition by deptno  ORDER BY ename, sex, age)  rn,  
                count(*) OVER (ORDER BY deptno)  rm
           FROM temp
      ) a1   
 WHERE a2.ename = a1.ename, 
       and a2.deptno   = a1.deptno  
       a2.birthday=a1.birthday 
       and 
       a2.sex=a1.sex  
       START WITH rn = 1  
       CONNECT BY PRIOR rn = rm - 1 

Answer 2

参考:
--构建测试表：--Sql脚本:create table test1(  ID   NUMBER ,  NAME VARCHAR2(10));create table test2(  ID   NUMBER ,  ROLE VARCHAR2(10));--测试数据insert into test1 (ID, NAME) values (1, '张三');insert into test1 (ID, NAME) values (2, '李四');commit;insert into test2 (ID, ROLE) values (1, '查询');insert into test2 (ID, ROLE) values (1, '分析');insert into test2 (ID, ROLE) values (1, '决策');insert into test2 (ID, ROLE) values (2, '查询');commit;--要求输出结果：      ID  NAME    ROLE      --------------------      1   张三       查询,分析,决策      2   李四       查询--方法一、使用wmsys.wm_concat--            它是oracle在10g及其以上版本提供的一个内置函数,可以实现两字串的连接,默认是以逗号(',')进行连接--Sql代码:select test1.*,wmsys.wm_concat(role) role from test1,test2where test1.id=test2.id group by test1.id,test1.NAME ;--方法二、使用sys_connect_by_path--            此函数也是实现字串的连接,在9i及以前就提供此函数,一般和connect by 语句结合来构造树状结构的查询--Sql代码:select id, name, max(ltrim(sys_connect_by_path(role, ','), ','))rolefrom  (select        row_number() over(partition by test1.id order by name) rn,        test1.*, role   from test1, test2  where test1.id =test2.id)start with rn = 1connect by prior rn = rn-1 and prior id = idgroup by id, nameorder by id ;--方法三、使用自定义函数--Sql代码--创建functioncreate or replace function my_concat(p_id in number)return varchar2is  result    varchar2(4000);begin       for rs in (select role from test2 where id=p_id) loop           result :=result || rs.role || ',';       end loop;       result := rtrim(result,',');  --去掉最后一个空格       return result;end;--查询sqlselect test1.*,my_concat(test1.id)role from test1,test2 where test1.id=test2.idgroup by test1.id,test1.nameorder by test1.id ;--拓展此函数create or replace function my_concat2(table_name in  varchar2,   --表名                                      p_col1     in  varchar2,   --查询条件的栏位名称                                      p_value    in  varchar2,   --查询条件的栏位值                                      p_col2     in  varchar2    --查询栏位，即连接栏位)return varchar2IS  type my_type is record(str varchar2(100));  type cur is ref cursor ;  my_cur    cur;  rs        my_type;  result    varchar2(4000);  --返回字符串连接后的结果集  sql_str   varchar2(4000);  --动态查询sql语句begin       sql_str:='select '||p_col2||' from '||table_name||' where '||p_col1||'='||p_value;       open my_cur for sql_str;       loop          fetch my_cur into rs;                exit when my_cur%notfound;                result :=result || rs.str || ',';       end loop;       result := rtrim(result,',');  --去掉最后一个空格       return result;end;SELECT id,NAME, my_concat2('test2','id',id,'role')role  FROM test1;如果是oralce 10g及以上,可利用wm_concat合并数据.

Answer 3

多谢2楼，我试了下。但是会出现这样的结果。
10 F 2011-09-28 aaa
10 F 2011-09-29 aaa,bbb
10 M 2011-09-28 ccc
20 M 2011-09-28 ddd,eee,fff
30 X 2011-09-28 hhh,ggg,yyy,ttt
第二行中会把前一条的人名给算进来。
我想根据前面三个字段分组，然后将分组得到的结果中人名那列都保存到一行的一个字段中。
目前知道根据一个字段分组是没问题的，但是想扩展到多个字段就出现了上述问题，
不知道原因出现在哪里？多谢指教。