create table score_line
(
sname varchar2(20)
,subject varchar2(20)
,score number(3)
);
create table score_col
(
sname varchar2(20)
,yuwen number
,shuxue number
,yingyu number
);
-- 列转行
-- 方法1 union all
select s.sname,‘数学’as subject,s.shuxue as score from score_col s
union all
select s.sname,‘英语’ as subject,s.yingyu as score from score_col s;
--方法2 oracle 自带的 列转行 函数
select sname,subject,score
from score_col
unpivot ( score for subject in(yuwen,shuxue,yingyu) );
-- 示例
select sname,subject,score
from score_col
unpivot ( score for subject in(yuwen as ‘语文’,shuxue as ‘数学’,yingyu as ‘英语’) );
-- 行转列
-- 方法1 关联
-- 方法2 decode
select s.sname
,sum(decode(s.subject,‘语文’,s.score,0)) as yuwen
,sum(decode(s.subject,‘数学’,s.score,0)) as shuxue
,sum(decode(s.subject,‘英语’,s.score,0)) as yingyu
from score_line s
group by s.sname;
-- 方法3 case when
select s.sname
,sum(case when s.subject = ‘语文’ then s.score else 0 end ) as yuwen
,sum(case when s.subject = ‘数学’ then s.score else 0 end ) as shuxue
,sum(case when s.subject = ‘英语’ then s.score else 0 end ) as yingyu
from score_line s
group by s.sname;
-- 方法4 oracle 自带的 行转列 函数
select *
from score_line
pivot ( sum(score) for subject in(‘语文’ as yuwen,‘数学’ as shuxue,‘英语’ as yingyu) );