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

多行记录合并到一个单元格
 
有这样一个问题

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

然后得到的结果是

no text
1 'aa'
1 'bb'

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

[解决办法]

SQL code

WITH tb AS(SELECT 1 a,'aa' b FROM dualUNION ALLSELECT 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
[解决办法]
我的示例代码，亲测，呵呵。
SQL code
-- 假定NO列为主键CREATE TABLE A(    NO      VARCHAR(2),    Text    VARCHAR(20));CREATE TABLE B(    NO      VARCHAR(2),    Text    VARCHAR(20));INSERT INTO A VALUES('1', 'aa');INSERT INTO A VALUES('2', 'xx');INSERT INTO B VALUES('1', 'bb');INSERT INTO B VALUES('2', 'yy');SELECT NO AS NO, a.Text || '+' ||b.Text AS Text FROM A JOIN B USING(NO);