一、创建AIR(航空信息)
CREATE TABLE AIR
( NUM VARCHAR2(20),
AIR_CODE CHAR(2)
);
二、新增测试数据
insert into AIR values(1,'CA');
insert into AIR values(1,'HA');
三、通过case..when方式实现行转列
select num,
(case when a='1' then 'CA' end) a,
(case when b=0 then 'HA' end) b
from(
select num,
sum((case when air_code='CA' then 1 end)) a,
sum((case when air_code='HA' then 0 end)) b
from air group by num
);
四、通过left join..on方式实现行转列
select t.num,t.air_code,tt.air_code from (select * from air a where A.Air_Code = 'CA') t
left join (select * from air aa where aa.Air_Code = 'HA') tt
on t.num = tt.num
where t.num = 1;
转载于:https://my.oschina.net/majg/blog/512805