SQL Server中行列转换 Pivot UnPivot

最新推荐文章于 2023-05-18 12:15:57 发布

「已注销」

最新推荐文章于 2023-05-18 12:15:57 发布

阅读量1k

点赞数

分类专栏： SQL SERVER

SQL SERVER 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作，将表值表达式的列转换为列值。

注意
对升级到 SQL Server 2005 或更高版本的数据库使用 PIVOT 和 UNPIVOT 时，必须将数据库的兼容级别设置为 90 或更高。有关如何设置数据库兼容级别的信息，请参阅sp_dbcmptlevel (Transact-SQL)。

PIVOT 提供的语法比一系列复杂的 SELECT...CASE 语句中所指定的语法更简单和更具可读性。有关 PIVOT 语法的完整说明，请参阅 FROM (Transact-SQL)。

以下是带批注的 PIVOT 语法。

SELECT <非透视的列>,

[第一个透视的列] AS <列名称>,

[第二个透视的列] AS <列名称>,

...

[最后一个透视的列] AS <列名称>,

FROM

(<生成数据的 SELECT 查询>)

AS <源查询的别名>

PIVOT

(

<聚合函数>(<要聚合的列>)

FOR

[<包含要成为列标题的值的列>]

IN ( [第一个透视的列], [第二个透视的列],

... [最后一个透视的列])

) AS <透视表的别名>

<可选的 ORDER BY 子句>;

简单 PIVOT 示例

下面的代码示例生成一个两列四行的表。

 
      USE AdventureWorks2008R2 ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost 
FROM Production.Product
GROUP BY DaysToManufacture;

下面是结果集：

DaysToManufacture AverageCost

0 5.0885

1 223.88

2 359.1082

4 949.4105

没有定义 DaysToManufacture 为 3 的产品。

以下代码显示相同的结果，该结果经过透视以使 DaysToManufacture 值成为列标题。提供一个列表示三 [3] 天，即使结果为 NULL。

 
      -- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, 
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost 
    FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;

下面是结果集：

Cost_Sorted_By_Production_Days 0 1 2 3 4

AverageCost 5.0885 223.88 359.1082 NULL 949.4105

复杂 PIVOT 示例

可能会用到 PIVOT 的常见情况是：需要生成交叉表格报表以汇总数据。例如，假设需要在 AdventureWorks2008R2 示例数据库中查询 PurchaseOrderHeader 表以确定由某些特定雇员所下的采购订单数。以下查询提供了此报表（按供应商排序）。

 
   
 
     
 
      USE AdventureWorks2008R2;
GO
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5
FROM 
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [250], [251], [256], [257], [260] )
) AS pvt
ORDER BY pvt.VendorID;
 
     

以下为部分结果集。

VendorID Emp1 Emp2 Emp3 Emp4 Emp5

1492 2 5 4 4 4

1494 2 5 4 5 4

1496 2 4 4 5 5

1498 2 5 4 4 4

1500 3 4 4 5 4

将在 EmployeeID 列上透视此嵌套 select 语句返回的结果。

 
      SELECT PurchaseOrderID, EmployeeID, VendorID
FROM PurchaseOrderHeader;

这意味着 EmployeeID 列返回的唯一值自行变成了最终结果集中的字段。因此，在透视子句中指定的每个 EmployeeID 号都有相应的一列：在本例中为雇员164、198、223、231 和 233。PurchaseOrderID 列作为值列，将根据此列对最终输出中返回的列（称为分组列）进行分组。在本例中，通过 COUNT 函数聚合分组列。请注意，将显示一条警告消息，指出为每个雇员计算 COUNT 时未考虑显示在 PurchaseOrderID 列中的任何空值。

重要提示
如果聚合函数与 PIVOT 一起使用，则计算聚合时将不考虑出现在值列中的任何空值。

UNPIVOT 将与 PIVOT 执行几乎完全相反的操作，将列转换为行。假设以上示例中生成的表在数据库中存储为 pvt，并且您需要将列标识符 Emp1、Emp2、Emp3、Emp4 和Emp5 旋转为对应于特定供应商的行值。这意味着必须标识另外两个列。包含要旋转的列值（Emp1、Emp2...）的列将被称为 Employee，将保存当前位于待旋转列下的值的列被称为 Orders。这些列分别对应于 Transact-SQL 定义中的 pivot_column 和 value_column。以下为该查询。

 
   
 
     
 
      --Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
    Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO
 
     

以下为部分结果集。

VendorID Employee Orders

---------- ---------- ------

1 Emp1 4

1 Emp2 3

1 Emp3 5

1 Emp4 4

1 Emp5 4

2 Emp1 4

2 Emp2 1

2 Emp3 5

2 Emp4 5

2 Emp5 5

...

请注意，UNPIVOT 并不完全是 PIVOT 的逆操作。PIVOT 会执行一次聚合，从而将多个可能的行合并为输出中的单个行。而 UNPIVOT 不会重现原始表值表达式的结果，因为行已经被合并了。另外，UNPIVOT 的输入中的空值不会显示在输出中，而在执行 PIVOT 操作之前，输入中可能有原始的空值。

AdventureWorks2008R2 示例数据库中的 Sales.vSalesPersonSalesByFiscalYears 视图将使用 PIVOT 返回每个销售人员在每个会计年度的总销售额。若要在 SQL Server Management Studio 中编写视图脚本，请在“对象资源管理器”中，在“视图”文件夹下找到 AdventureWorks2008R2 数据库对应的视图。右键单击该视图名称，再选择“编写视图脚本为”。

***************************************************************************************************************************

在Sql Server的帮助文档中，对Pivot函数是这样解释的：
可以使用 PIVOT 和 UNPIVOT 关系运算符对表值表达式进行操作以获得另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来转换表值表达式，并在必要时对最终输出中所需的任何其余的列值执行聚合.

对第一次使用PIVOT函数的朋友来说，这样的解释很难让大家理解，下面编辑用PIVOT函数来实现一个行转列的功能，以便让读者更容易理解该函数.

注意：PIVOT是Sql Server2005的新函数，2005前行转列请参看本站：
SQLServer中(行列转换)行转列及列转行且加平均值及汇总值

先创建一个工资表:

Create Table Salary
(
HrName varchar(50),
Monthly varchar(50),
Money money
)

往表中插入数据：

insert into Salary(HrName,Monthly,[Money])
select '张三','一月','3000'
union all
select '张三','二月','3200'
union all
select '张三','三月','3500'
union all
select '李四','一月','3800'
union all
select '李四','二月','4200'
union all
select '李四','三月','3900'
union all
select '张三','一月','2000'

查看正常的数据：

select * from Salary

结果：

HrName Monthly Money
张三    一月    3000.00
张三    二月    3200.00
张三    三月    3500.00
李四    一月    3800.00
李四    二月    4200.00
李四    三月    3900.00
张三    一月    2000.00

查看行转列后的数据：

select HrName as '姓名',[一月],[二月],[三月] from Salary
pivot(sum([Money]) for Monthly in ([一月],[二月],[三月])) as pvt

结果：

姓名   一月     二月     三月
李四 3800.00   4200.00 3900.00
张三 5000.00   3200.00 500.00

注意：
pivot(sum([Money]) for Monthly in ([一月],[二月],[三月])) 中的sum([Money])，这里必须是聚合函数，比如是min,max等。
in ([一月],[二月],[三月])中的[一月],[二月],[三月]即为Monthly的Value,又为新结果集的列名.

如果我们将其中的一月改为四月，因为数据源中没有四月的记录，所以四月查询出来应该为Null.
测试：

select HrName as '姓名',[四月],[二月],[三月] from Salary
pivot(sum([Money]) for Monthly in ([四月],[二月],[三月])) as pvt

结果：

姓名   四月    二月     三月
李四   NULL   4200.00   3900.00
张三   NULL   3200.00   3500.00

以上为编辑个人理解小结，仅作参考！

本文来源于:http://www.lmwlove.com/ac/ID551

「已注销」

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL Server中行列转换 Pivot UnPivot

PIVOT用于将列值旋转为列名（即行转列），在SQL Server2000可以用聚合函数配合CASE语句实现PIVOT的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P完整语法：table_sourcePIVOT(聚合函数（value_column）FOR pivot_columnIN()) UNPIVOT用于将列明转为列
复制链接

扫一扫