1.简单案例
create database Hang
go
use Hang
create table Students
(
Name varchar(50),
Kemu varchar(50),
sor int
)
insert into Students values('张三','语文',8888)
insert into Students values('张三','语文',85)
insert into Students values('张三','数学',75)
insert into Students values('李四','数学',95)
i nsert into Students values('李四','语文',65)
select * from Students
----------------行转列-------------
select * from Students pivot(max(sor) for Kemu in(语文,数学))
as t
----------------列转行----------------
with Lie as (
select * from Students pivot(max(sor) for Kemu in(语文,数学))
as t
)
select *from Lie unpivot (sor for Kemu 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