关于oracle的一个行转列案例
如下图所示
第一个数据表经过sql语句需要转换成第二个数据库表
方法一：
采用case when 转换
sql语句：select name,
max(case course when ‘JDBC’ then grade else 0 end) jdbc,
max( case course when ‘SPRING’ then grade else 0 end) spring,
max(case course when ‘HIBERNATE’ then grade else 0 end) hibernate
from tb_cource group by name;
方法二：
采用decode 函数
sql语句： select name,
max(decode(course, ‘JDBC’, grade, 0)) as JDBC,
max(decode(course, ‘SPRING’, grade, 0)) as SPRING,
max(decode(course, ‘HIBERNATE’, grade, 0)) as HIBERNATE
from tb_cource
group by name;
方法三：
采用临时表的方式
先建立一个临时表，把数据插入到临时表中，然后，建立表2，表2从临时表中去取.代码如下：
create table test_temp(
name varchar2(10),
spring number(10),
jdbc number(10),
hibernate number(10)
);
见表一插入临时表
insert into test_temp(
name,
spring,
jdbc,
hibernate
)
select t.name,sum(t.spring),sum(t.jdbc),sum(t.hibernate)
from (
select
name ,
t.grade as spring,
0 as jdbc,
0 as hibernate
from 表一 t
where t.course =’spring’
union all
name ,
0 as spring,
t.grade as jdbc,
0 as hibernate
from 表一 t
where t.course =’jdbc’
name ,
0 as spring,
0 as jdbc,
t.grade as hibernate
from 表一 t
where t.course =’hibernate’
) t group by t.name