求一条sql问

Question

请教一个sql文
表如下
   NO      ＳＩＣ    AREA
VARCHAR2 VARCHAR2 VARCHAR2 
910802011  91       19 
910802011  91       24 
910801011  91       19 
910801011  91       48 
910801011  91       50 
要得到这样的数据，取同一NO的所有AREA
  NO          AREA
910802011    19,24 
910801011    19,48,50 
不用存储过程，用SQL文能不能实现。

Answer 1

可以的，你查下以前的帖子有下面是例子
Oracle9i中使用SYS_CONNECT_BY_PATH进行行列转换- -
有表：
SQL> SELECT deptno, ename FROM emp ORDER BY deptno, ename;DEPTNO ENAME
------ ----------
    10 CLARK
    10 KING
    10 MILLER
    20 ADAMS
    20 FORD
    20 JONES
    20 SCOTT
    20 SMITH
    30 ALLEN
    30 BLAKE
    30 JAMES
    30 MARTIN
    30 TURNER
    30 WARD14 rows selected.
想输出为：
DEPTNO ENAME
------ ----------
    10 CLARK, KING, MILLER
    20 ADAMS, FORD, JONES, SCOTT, SMITH
    30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD 除了使用聚集函数或者存储过程之外，9i中可以：
    SQL> SELECT deptno  
    2       , LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))  
    3         KEEP (DENSE_RANK LAST ORDER BY curr),',') AS concatenated  
    4  FROM   ( SELECT deptno  
    5                , ename  
    6                , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr  
    7                , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev  
    8           FROM   emp )  
    9  GROUP BY deptno 
    10  CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno 
    11  START WITH curr = 1;    
    DEPTNO----------CONCATENATED----------------------------------------------------------------------------------------------------        
    10CLARK,KING,MILLER        
    20ADAMS,FORD,JONES,SCOTT,SMITH        
    30ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD10CLARK,KING,MILLER        20ADAMS,FORD,JONES,SCOTT,SMITH        30ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

Answer 2

oracle   怎样实现合并相同行   希望是一条语句 
如 
create   table   tb 
( 
    id   number,     
    name   varchar2(100) 
); 
insert   into   tb   values(1,'aa'); 
insert   into   tb   values(2,'bb'); 
insert   into   tb   values(3,'cc'); 
insert   into   tb   values(1,'dd'); 
insert   into   tb   values(1,'ee'); 实现   
1         aa,dd,ee 
2         bb 
3         cc         
--------------------------create or replace function t_f                                                                    
(                                                                               
  v_id number                                                                   
)                                                                               
return varchar2                                                                 
is                                                                              
  result varchar2(5000);                                                        
begin                                                                           
 result := '';                                                                  
 for x in (select name from tb where id = v_id)                                 
 loop                                                                           
     result := result || x.name || ',';                                         
 end loop;                                                                       result := substr(result, 1, length(result) - 1);                               
 return result;                                                                 
end t_f;              
select     id,   t_f(id)   as   name   from   (select   distinct   id   from   tb)   x 

Answer 3

是CSDN里，别人写的。我只是保存下来了