SQL中PIVOT的用法

PIVOT 提供的语法比一系列复杂的 SELECT…CASE 语句中所指定的语法更简单和更具可读性。

以下是带批注的 PIVOT 语法：

SELECT <非透视的列>,
    [第一个透视的列] AS <列名称>,
    [第二个透视的列] AS <列名称>,
    ...
    [最后一个透视的列] AS <列名称>,
FROM
    (<生成数据的 SELECT 查询>)
    AS <源查询的别名>
PIVOT
(
    <聚合函数>(<要聚合的列>)
FOR
[<包含要成为列标题的值的列>]
    IN ( [第一个透视的列], [第二个透视的列],
    ... [最后一个透视的列])
) AS <透视表的别名>
<可选的 ORDER BY 子句>; 

原文链接：https://blog.csdn.net/yx183/article/details/82423420

让我们先从一个虚构的场景中来着手吧
万国来朝，很多供应商每天都汇报各自的收入情况。先来创建一个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 top 10 * from DailyIncome

如图所示：

虽然数据是能够完全给展示了，但好像一眼望去不能得到对我们用处更大的信息，比如说我们想得到每个供应商的每天的总收入，这时我们应该做一些数据形式的转变了，平常的所用的是这样的。

select VendorId ,
sum(case when  IncomeDay='MoN' then IncomeAmount else 0 end) MON,
sum(case when  IncomeDay='TUE' then IncomeAmount else 0 end) TUE,
sum(case when  IncomeDay='WED' then IncomeAmount else 0 end) WED,
sum(case when  IncomeDay='THU' then IncomeAmount else 0 end) THU,
sum(case when  IncomeDay='FRI' then IncomeAmount else 0 end) FRI,
sum(case when  IncomeDay='SAT' then IncomeAmount else 0 end) SAT,
sum(case when  IncomeDay='SUN' then IncomeAmount else 0 end) 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
第二步：要明白要想让哪一列的值做新的列名字
第三步：要明白对于这新的列要求那些值呢？

转载 https://www.jianshu.com/p/8f929264995e

用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 = 2 WHERE 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 = 1 WHERE Pivot_column = 'A'

update Pivot_test set id1 = 2 WHERE Pivot_column = 'A'

select * from Pivot_test PIVOT(MAX(value) for Pivot_column in (A,B,C,D)) tem

以上证明pivot是以除了Pivot column 和value的其他所有列作为分组

转载 https://www.cnblogs.com/qianlixing/p/6648622.html