SQL server 行转列 列转行

时间:2021-02-17 10:44:47

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