pivot和unpivot

drop table test
create table test
(id int,
name varchar(20),quarter int,profile int)
insert into test values(1,'a',1,1000)
insert into test values(1,'a',2,2000)
insert into test values(1,'a',3,4000)
insert into test values(1,'a',4,5000)
insert into test values(2,'b',1,3000)
insert into test values(2,'b',2,3500)
insert into test values(2,'b',3,4200)
insert into test values(2,'b',4,5500)
select * from test
--
select id,name,sum(case quarter when 1 then profile end) as "一季度",
sum(case quarter when 2 then profile end) as "二季度",
sum(case quarter when 3 then profile end) as "三季度",
sum(case quarter when 4 then profile end) as "四季度"
from test
group by id,name

--使用PIVOT将四个季度的利润转换成横向显示:
select id,name,
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度"
from test
pivot
(
sum(profile)
for quarter in
([1],[2],[3],[4])
)as pvt

使用UNPIVOT

create table productsale2
(id int,
name varchar(20),
q1 int,q2 int, q3 int, q4 int
)

insert into productsale2 values(1,'a',1000,2000,3000,4000)
insert into productsale2 values(2,'b',3000,3500,4200,5500)

SELECT ID,Name,Quarter,Sale
FROM ProductSale2
UNPIVOT --使用UNPIVOT列转行
(
Sale
for Quarter in
([Q1],[Q2],[Q3],[Q4])
)
as unpvt

--demo 2
create table tb(name varchar(10) , km varchar(10) , cj int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go

select name as '姓名' ,
max(case km when '语文' then cj else 0 end) 语文,
max(case km when '数学' then cj else 0 end) 数学,
max(case km when '物理' then cj else 0 end) 物理
from tb
group by name
go

select name ,[语文] as '语文',[数学] as '数学',
[物理] as '物理'
from tb
pivot ( sum (cj) for km in ([语文],[数学],[物理])) as pvt