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

[Jason-Jin]

有这样一个问题

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

然后得到的结果是

no text
1 'aa'
1 'bb'

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

[yixilan]

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

[cosio]

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

[dawugui]

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  

[summer353]

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

[我心飞翔]

我的示例代码，亲测，呵呵。

-- 假定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);

结果：