with t as (select 1 donghao, 'fangjian' leibie, 101 haoma, 100 mianji, 'aa1' yonghu from dual union all select 1 donghao, 'fangjian' leibie, 103 haoma, 100 mianji, 'aa2' yonghu from dual union all select 1 donghao, 'qiku' leibie, 104 haoma, 100 mianji, 'aa3' yonghu from dual union all select 2 donghao, 'fangjian' leibie, 105 haoma, 100 mianji, 'aa4' yonghu from dual union all select 2 donghao, 'qiku' leibie, 106 haoma, 100 mianji, 'aa5' yonghu from dual union all select 3 donghao, 'qiku' leibie, 107 haoma, 100 mianji, 'aa6' yonghu from dual union all select 4 donghao, 'fangjian' leibie, 108 haoma, 100 mianji, 'aa7' yonghu from dual) select decode(t1.donghao, null, t2.donghao, t1.donghao) num, t1.fangjian, t1.mianji, t1.yonghu, t2.qiku, t2.mianji, t2.yonghu from (select t.donghao, haoma fangjian, mianji, yonghu, row_number() over(partition by donghao order by donghao) rn from t where t.leibie = 'fangjian') t1 full outer join (select t.donghao, haoma qiku, mianji, yonghu, row_number() over(partition by donghao order by donghao) rn from t where t.leibie = 'qiku') t2 on t1.donghao = t2.donghao and t1.rn = t2.rn order by num;
(select 1 donghao, 'fangjian' leibie, 101 haoma, 100 mianji, 'aa1' yonghu
from dual
union all
select 1 donghao, 'fangjian' leibie, 103 haoma, 100 mianji, 'aa2' yonghu
from dual
union all
select 1 donghao, 'qiku' leibie, 104 haoma, 100 mianji, 'aa3' yonghu
from dual
union all
select 2 donghao, 'fangjian' leibie, 105 haoma, 100 mianji, 'aa4' yonghu
from dual
union all
select 2 donghao, 'qiku' leibie, 106 haoma, 100 mianji, 'aa5' yonghu
from dual
union all
select 3 donghao, 'qiku' leibie, 107 haoma, 100 mianji, 'aa6' yonghu
from dual
union all
select 4 donghao, 'fangjian' leibie, 108 haoma, 100 mianji, 'aa7' yonghu
from dual)
select decode(t1.donghao, null, t2.donghao, t1.donghao) num,
t1.fangjian,
t1.mianji,
t1.yonghu,
t2.qiku,
t2.mianji,
t2.yonghu
from (select t.donghao,
haoma fangjian,
mianji,
yonghu,
row_number() over(partition by donghao order by donghao) rn
from t
where t.leibie = 'fangjian') t1
full outer join (select t.donghao,
haoma qiku,
mianji,
yonghu,
row_number() over(partition by donghao order by donghao) rn
from t
where t.leibie = 'qiku') t2
on t1.donghao = t2.donghao
and t1.rn = t2.rn
order by num;