多行记录合并到一个单元格

 
有这样一个问题

假如A表和B表关联 A.no=B.no

然后得到的结果是

no text
1 'aa'
1 'bb'

我想将这两个结果拼成一个结果
就是说
得到
no text
1 'aa'+'bb'
其中还有+号，如何办

select a.no, a.text ||'+'|| b.text from (select 1 as no, 'aa' as text from dual 
union 
select 2 as no, 'cc' as text from dual) a, 
(select 1 as no, 'bb' as text from dual) b
where a.no = b.no

SQL code

WITH tb AS
(SELECT 1 a,'aa' b FROM dual
UNION ALL
SELECT 1,'bb' FROM dual
)
SELECT a,max(substr(sys_connect_by_path(b,'+'),2))
FROM 
(
       SELECT a,b,row_number()over(PARTITION BY a ORDER BY a) rn
       FROM tb
) START WITH rn=1
  CONNECT BY rn-1= PRIOR rn AND a=PRIOR a
  GROUP BY a

--result:

1    aa+bb

SQL code

with tb as(  
     select 'A' grade,'XX'name from dual union all  
     select 'A','XY' from dual union all  
     select 'A','YY' from dual union all  
     select 'B','aa' from dual union all  
     select 'B','bb' from dual)  
select grade,substr(max(sys_connect_by_path(name,';')),2) name  
from (select grade,name,row_number() over(partition by grade order by name) rn from tb)  
start with rn=1  
connect by  rn= prior rn+1  and   
            connect_by_root(grade)=grade  
group by grade;  
--   
GRADE NAME  
----- --------------------------------------------------------------------------------  
A     XX;XY;YY  
B     aa;bb  
--  
10g的实现：  
with t as(  
     select 'A' grade,'XX'name from dual union all  
     select 'A','XY' from dual union all  
     select 'A','YY' from dual union all  
     select 'B','aa' from dual union all  
     select 'B','bb' from dual)  
select grade,wmsys.wm_concat(name) name  
from t  
group by grade;  
--  
GRADE NAME  
----- --------------------------------------------------------------------------------  
A     XX,XY,YY  
B     aa,bb  

select no, replace(wm_concat(text),',','+') from table_name group by no