数据库 SQL 横竖表 互转

创建竖表
create table score
(
     id int primary key,
     s_name varchar(10),
     course varchar(20),
     score int default 0
)
插入数据
insert into score(id,s_name,course,score) values (1,'Jon','English','78');
insert into score(id,s_name,course,score) values (2,'Jon','mathematics','88');
insert into score(id,s_name,course,score) values (3,'Jon','language','98');


insert into score(id,s_name,course,score) values (4,'Tom','English','18');
insert into score(id,s_name,course,score) values (5,'Tom','mathematics','28');
insert into score(id,s_name,course,score) values (6,'Tom','language','38');


insert into score(id,s_name,course,score) values (7,'God','English','78');
insert into score(id,s_name,course,score) values (8,'God','mathematics','88');
insert into score(id,s_name,course,score) values (9,'God','language','98');


创建横表
create table score2
(
     id int primary key,
     s_name varchar(10),
     English varchar(20),
     mathematics varchar(20),
     language varchar(20)
)
插入数据
insert into score2(id,s_name,English,mathematics,language) values (1,'Jon','78','88','98');
insert into score2(id,s_name,English,mathematics,language) values (2,'Tom','18','28','38');
insert into score2(id,s_name,English,mathematics,language) values (3,'God','78','88','98');


竖表转横表
select s_name,
max(decode(course,'English',score))English,
max(decode(course,'mathematics',score))mathematics,
max(decode(course,'language',score))language
from score
group by s_name
order by s_name


竖表转横表
select * from score;/*竖表*/
select t1.s_name,t1.English,t2.mathematics,t3.language from
(select s_name,score as English from score where course = 'English') t1
left join(
select s_name,score as mathematics from score where course = 'mathematics') t2 
on t1.s_name = t2.s_name
left join(
select s_name,score as language from score where course = 'language') t3 
on t2.s_name = t3.s_name


横表转竖表
select s_name,'English' as course ,English from score2
union all 
select s_name,'mathematics' as course,mathematics from score2
union all
select s_name,'language' as course,language from score2