日期:2014-05-17  浏览次数:20795 次

高手请进,请教一个sql
请问如何将一个表中的不同记录合并到一个记录中???  

如我现在的表结构Test为  
ID   DATA  
1   A  
1   B  
2   C  
2   D  
2   E  
2   F  

希望将其转换Test2为  
ID   DATA  
1   A,B  
2   C,D,E,F

------解决方案--------------------
create or replace procedure proc_test_test2
(v_c1 in varchar2,
v_result out varchar2)
as
cursor cur_ is
select data from table1
where id=v_c1;
begin
for v_c2 in cur_ loop
v_result := v_result|| ', '||v_c2.data;
end loop;
end;

这个试试
------解决方案--------------------
select b.id,
(select ltrim(max(sys_connect_by_path(a.data, ', ')), ', ') data
from (select a.id,
a.data,
a.n,
lead(a.n) over(partition by a.id order by a.n) n1
from (select a.id,
a.data,
row_number() over(order by a.id, a.data desc) n
from test a) a) a
start with a.id = b.id
and a.n1 is null
connect by n1 = prior a.n) v1
from (select distinct a.id from test a) b