oracle中行转列操作

数据准备阶段：

CREATE TABLE CC (Student NVARCHAR2(2),Course NVARCHAR2(2),Score INT);

INSERT into CC
select N'张三',N'语文',78 from dual union all
select N'张三',N'数学',87 from dual union all
select N'张三',N'英语',82 from dual union all
select N'张三',N'物理',90 from dual union all
select N'李四',N'语文',65 from dual union all
select N'李四',N'数学',77 from dual union all
select N'李四',N'英语',65 from dual union all
select N'李四',N'物理',85 from dual ;
commit;

操作如下：

方法一、

select

student,

sum(decode(course,'语文',score)) 语文,

sum(decode(course,'数学',score)) 数学,

sum(decode(course,'英语',score)) 英语,

sum(decode(course,'物理',score)) 物理,

sum(score)

from cc group by student；

方法二、

SELECT KIN.*,

  KIN.a+KIN.b+KIN.c+KIN.d AS TOTAL

FROM

  (SELECT   *

  FROM CC PIVOT ( MAX(SCORE) FOR COURSE IN ('语文' AS A , '数学' AS B, '英语' AS C,'物理' AS D) )

  ) KIN;

方法三、

select

student，

sum(case when course = '语文' then score else 0 end )语文,

sum(case when course = '数学' then score else 0 end )数学,

sum(case when course = '英语' then score else 0 end )英语,

sum(case when course = '物理' then score else 0 end )物理

from cc group by student;

完。

巴特西

oracle中行转列操作

最新文章

热门文章