1.简单案例
create table student
(
sid int primary key identity(1,1), --主键自增
sName varchar(20), --学生姓名
)
select * from student
create table class
(
cid int primary key identity(1,1), --主键自增
cName varchar(20)
)
select* from class
create table score
(
scid int primary key identity(1,1), --主键自增
scName int,
sid int,
cid int
)
select * from score
select * from
(
select a.sName,b.scName,c.cName from student as a
inner join score as b on a.[sid]=b.[sid]
inner join class as c on b.cid=c.cid
) as P
pivot
(
sum(P.scName) for
P.cName in (语文,数学,英语)
) as T
2.另一案例
select Name as 水果,
max(case RegionName when '北京' then Price else 0 end) 北京,
max(case RegionName when '广州' then Price else 0 end) 广州
from (
select f.Name,r.RegionName,rf.Price from Fruits f
join RegionPrice rf on f.ID =rf.FruitID
join Regions r on rf.RegionID =r.id
) tb group by Name
select f.Name,r.RegionName,rf.Price from Fruits f
join RegionPrice rf on f.ID =rf.FruitID
join Regions r on rf.RegionID =r.id
select Name as 水果,
case RegionName when '北京' then Price else 0 end 北京,
case RegionName when '广州' then Price else 0 end 广州
from (
select f.Name,r.RegionName,rf.Price from Fruits f
join RegionPrice rf on f.ID =rf.FruitID
join Regions r on rf.RegionID =r.id
) tb
select * from
(
select f.Name,r.RegionName,rf.Price from Fruits f
join RegionPrice rf on f.ID =rf.FruitID
join Regions r on rf.RegionID =r.id
) tb
pivot
(
max(tb.Price) for tb.RegionName in
([广州],[北京])
) as a