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