行列转换

create table test(id varchar2(10),mc varchar2(50));

insert into test values('1','11111');
insert into test values('1','22222');
insert into test values('2','11111');
insert into test values('2','22222');
insert into test values('3','11111');
insert into test values('3','22222');
insert into test values('3','33333');

select id,mc,row_number() over(partition by id order by id) rn_by_id,
row_number() over (order by id) + id rn from test;

-----------------------------------
select id,ltrim(max(sys_connect_by_path(mc,';')),';') add_mc from
(
select id,mc,row_number() over(partition by id order by id) rn_by_id,
row_number() over (order by id) + id rn from test

)
start with rn_by_id = 1 connect by rn - 1 = prior rn
group by id
order by id;
------------------------------------

SELECT distinct id,ltrim(first_value(mc_add) over (partition by id order BY l DESC),';')
from (
SELECT id,LEVEL l,sys_connect_by_path(mc,';') mc_add from
(
select id||rownum rn,id||rownum-1 rn_small,id,mc from test
)
CONNECT BY PRIOR rn = rn_small
)
;
--------------------------------------

select id,ltrim(max(sys_connect_by_path(mc,';')),';') from(
select id,mc,row_number() over(partition by id order by id) id1,
row_number() over(order by id) + dense_rank() over(order by id) id2
from test
)
start with id1=1 connect by prior id2 = id2 -1
group by id order by id;

---------------------------------------

select * from test

select sys_connect_by_path (mc,';') from test
CONNECT BY prior mc=id

-----------------------------------------
Select  id,max(Substr(Sys_Connect_By_Path(mc, ','), 2)) Test
From (

)

Group by id

Start With Rid = 1
Connect By Prior Next_Rid = Rid
order by id