数据库之横表转纵表、纵表转横表
时间：2021-07-01 10:21:17
帮助过：4人阅读
纵表转横表
create table biao
(
PName varchar(10),
PCourse varchar (10),
PGrade int
)
go
insert into biao values(‘张三‘,
‘语文‘,
‘95‘)
insert into biao values(‘张三‘,
‘数学‘,
‘90‘)
insert into biao values(‘张三‘,
‘英语‘,
‘89‘)
insert into biao values(‘李四‘,
‘语文‘,
‘92‘)
insert into biao values(‘李四‘,
‘数学‘,
‘88‘)
insert into biao values(‘李四‘,
‘英语‘,
‘97‘)
select*
from biao
--
下面输入转换方式
select PName ,
SUM(case PCourse when
‘语文‘then PGrade
else 0 end)
as 语文,
SUM(case PCourse when
‘数学‘then PGrade
else 0 end)
as 数学,
SUM(case PCourse when
‘英语‘then PGrade
else 0 end)
as 英语
from biao
group by PName
--横表转纵表
--例2创建表2
create table biao1
(
姓名 varchar(20),
语文 int,
数学 int,
英语 int
)
go
insert into biao1 values(‘张三‘,95,90,89)
insert into biao1 values(‘李四‘,92,88,97)
select * from biao1
--下面输入转换方式
select * from biao1
select 姓名 ,‘语文‘ as 课程,语文 as 成绩 from biao1 union all
select 姓名,‘数学‘as 课程,数学 as 成绩 from biao1 union all
select 姓名,‘英语‘as 课程,英语 as 成绩 from biao1
order by 姓名 desc
数据库之横表转纵表、纵表转横表
标签：
