SQL中PIVOT的⽤法
PIVOT 提供的语法⽐⼀系列复杂的 SELECT…CASE 语句中所指定的语法更简单和更具可读性。以下是带批注的 PIVOT 语法：
exists的用法SELECT<⾮透视的列>,
[第⼀个透视的列]AS<；列名称>,
[第⼆个透视的列]AS<；列名称>,
...
[最后⼀个透视的列]AS<；列名称>,
FROM
(<⽣成数据的SELECT查询>)
AS<；源查询的别名>
PIVOT
(
<；聚合函数>(<；要聚合的列>)
FOR
[<；包含要成为列标题的值的列>]
IN([第⼀个透视的列],[第⼆个透视的列],
...[最后⼀个透视的列])
)AS<；透视表的别名>
<；可选的ORDER BY⼦句>;
让我们先从⼀个虚构的场景中来着⼿吧
万国来朝，很多供应商每天都汇报各⾃的收⼊情况。先来创建⼀个DailyIncome 表
create table DailyIncome
(VendorId nvarchar(10),
IncomeDay nvarchar(10), IncomeAmount int)
–VendorId 供应商ID,
–IncomeDay 收⼊时间
–IncomeAmount 收⼊⾦额
紧接着来插⼊数据看看
(留意看下，有的供应商某天中会有多次收⼊，应该是分批进账的)
insert into DailyIncome values('SPIKE','FRI',100)
insert into DailyIncome values('SPIKE','MON',300)
insert into DailyIncome values('FREDS','SUN',400)
insert into DailyIncome values('SPIKE','WED',500)
insert into DailyIncome values('SPIKE','TUE',200)
insert into DailyIncome values('JOHNS','WED',900)
insert into DailyIncome values('SPIKE','FRI',100)
insert into DailyIncome values('JOHNS','MON',300)
insert into DailyIncome values('SPIKE','SUN',400)
insert into DailyIncome values('JOHNS','FRI',300)
insert into DailyIncome values('FREDS','TUE',500)
insert into DailyIncome values('FREDS','TUE',200)
insert into DailyIncome values('SPIKE','MON',900)
insert into DailyIncome values('FREDS','FRI',900)
insert into DailyIncome values('FREDS','MON',500)
insert into DailyIncome values('JOHNS','SUN',600)
insert into DailyIncome values('SPIKE','FRI',300)
insert into DailyIncome values('SPIKE','WED',500)
insert into DailyIncome values('SPIKE','FRI',300)
insert into DailyIncome values('JOHNS','THU',800)
insert into DailyIncome values('JOHNS','SAT',800)
insert into DailyIncome values('SPIKE','TUE',100)
insert into DailyIncome values('SPIKE','THU',300)
insert into DailyIncome values('FREDS','WED',500)
insert into DailyIncome values('SPIKE','SAT',100)
insert into DailyIncome values('FREDS','SAT',500)
insert into DailyIncome values('FREDS','THU',800)
insert into DailyIncome values('JOHNS','TUE',600)
让我们先来看看前⼗⾏数据：
select top10*from DailyIncome
如图所⽰：
虽然数据是能够完全给展⽰了，但好像⼀眼望去不能得到对我们⽤处更⼤的信息，⽐如说我们想得到
每个供应商的每天的总收⼊，这时我们应该做⼀些数据形式的转变了，平常的所⽤的是这样的。
select VendorId ,
sum(case when  IncomeDay='MoN'then IncomeAmount else0end) MON,
sum(case when  IncomeDay='TUE'then IncomeAmount else0end) TUE,
sum(case when  IncomeDay='WED'then IncomeAmount else0end) WED,
sum(case when  IncomeDay='THU'then IncomeAmount else0end) THU,
sum(case when  IncomeDay='FRI'then IncomeAmount else0end) FRI,
sum(case when  IncomeDay='SAT'then IncomeAmount else0end) SAT,
sum(case when  IncomeDay='SUN'then IncomeAmount else0end) SUN
from DailyIncome group by VendorId
得到如下的结果：
case when结果
如果⼤家仔细看结果的话，会有这样的发现，这是把VendorID进⾏了分组，并且对于每组中IncomeDay这⼀列中的值都变成了新的列名字，然后对IncomeAmount进⾏求和操作。
这样写可能是有些⿇烦，别着急，我们⽤Pivot函数进⾏⾏转列试下。
select*from DailyIncome ----第⼀步
pivot
(
sum(IncomeAmount)----第三步
for IncomeDay in([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])---第⼆步
)
as AvgIncomePerDay
来解释下，要想⽤好Pivot函数，应该理解代码注释中的这⼏步。
第⼀步：肯定是要明⽩数据源了，这⾥是DailyIncome
第⼆步：要明⽩要想让哪⼀列的值做新的列名字
第三步：要明⽩对于这新的列要求那些值呢？
⽤pivot的时候，要注意
Pivot有⼀个隐藏的Group 分组， 除了Pivot column 和value列，其他列作为分组
Example:
IF NOT EXISTS(SELECT*FROM sys.tables where name ='Pivot_test')
CREATE TABLE Pivot_test
(
id1 int,
id2 int,
Pivot_column varchar(50),
value char(50)
)
insert into Pivot_test values(1,1,'A','A_V'),(1,1,'B','B_V'),(1,1,'C','C_V'),(1,1,'D','D_V')
⾏列转换
select*from Pivot_test
PIVOT(MAX(value)for Pivot_column in(A,B,C,D)) tem
更新A的id2值为2，再次⾏列转换，发现出现两⾏，证明id2影响了分组
update Pivot_test set id2 =2WHERE Pivot_column ='A'
select*from Pivot_test PIVOT(MAX(value)for Pivot_column in(A,B,C,D)) tem
更新A的id1值为2，id2更新为旧的值1，再次查看⾏列转换结果，发现结果仍为两⾏，证明id1也在分组中update Pivot_test set id2 =1WHERE Pivot_column ='A'
update Pivot_test set id1 =2WHERE Pivot_column ='A'
select*from Pivot_test PIVOT(MAX(value)for Pivot_column in(A,B,C,D)) tem
以上证明pivot是以除了Pivot column 和value的其他所有列作为分组

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。