不是必须要用一条SQL实现。用PLSQL实现也可以。table1(id varchar2(10), name varchar2(10))id name
1 aa
1 bb
1 cc
2 xx
3 yy
3 zz
...想得到一个结果集如下:
id names
1 aa+bb+cc
2 xx
3 yy+zz
...请教了。
1 aa
1 bb
1 cc
2 xx
3 yy
3 zz
...想得到一个结果集如下:
id names
1 aa+bb+cc
2 xx
3 yy+zz
...请教了。
create or replace type t_name as object
(
Id number(10),
Name varchar2(20),
);
create or replace type t_tab_names as table
of t_name;create or replace procedure p_test (p_tTab_names out t_tab_names) as
i number;
flag number;
vtemp varchar2(100);
tname t_name;
begin
i:=0;
p_tTab_names:=new t_tab_names();
for v in (select id from table1 group by id) loop
i:=i+1;
flag:=0;
for vv in (select id,name from table1 where id=v.id) loop
if (flag=0) then
vtemp:=vv.name;
else
vtemp:=vtemp||'+'||vv.name;
end if;
flag:=1;
end loop;
tname.id:=v.id;
tname.name:=vtemp;
p_tTab_names.extend;
p_tTab_names(i):=tname;
end loop;
exception when others then
dbms_output.put_line(sqlerrm);end p_test;
select distinct yy.id,
ltrim(first_value(yy.path) over(partition by yy.id order by yy.lev desc),'+') as name
from (
select zz.*,
sys_connect_by_path(zz.name,'+') as path,
level lev
from (
select tt.*,
tt.id || row_number() over(partition by tt.id order by tt.id)-1 as frontrn,
tt.id || row_number() over(partition by tt.id order by tt.id) as afterrn
from table1 tt
)zz
connect by prior zz.frontrn = zz.afterrn
)yy; ID NAME
---------- --------------------------------------------
1 cc+bb+aa
2 xx
3 zz+yy
- ---
1 aa
1 bb
1 cc
2 xx
2 yy
3 zz
3 mm已选择7行。SQL> select max(substr((sys_connect_by_path(name,',')),2)) col
2 from (
3 select id,name,
4 id+row_number() over( order by id) rn,
5 row_number() over(partition by id order by id) rn1
6 from a
7 )
8 start with rn1=1
9 connect by rn-1=prior rn
10 group by id
11 ;COL
--------------------------------------------------------------------------------aa,bb,cc
xx,yy
zz,mm
from (
select id,name,
rownum rnum,
row_number() over(partition by id order by id) rn1
from a
)
start with rn1=1
connect by rnum-1=prior rnum
group by id
;