Oracle-行列转化实际的工作应用

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) );