SQL PIVOT

SQL Server PIVOT 是最有用的运算符之一，用于将行值转换为列名，或者说是旋转表。在旋转表或透视表时，剩余的列值必须参与分组或聚合。

我们使用以下查询通过 SQL Server 透视表将行转换为列。虽然下面的查询易于理解，但为了避免进一步的复杂性，我们将数据复制到一个新表中。这也将让您更专注于 PIVOT 子句。

USE AdventureWorks2014
GO
SELECT PROD.Name,
       YEAR(OrdHead.OrderDate) AS [Order Year],
       SUM(Details.OrderQty) AS [Order Quantity]
  FROM Sales.SalesOrderDetail AS Details
     INNER JOIN
       Production.Product AS PROD ON
          Details.ProductID = PROD.ProductID
     INNER JOIN 
       Sales.SalesOrderHeader AS OrdHead ON
         Details.SalesOrderID = OrdHead.SalesOrderID
GROUP BY 
         PROD.Name,
         YEAR(OrdHead.OrderDate)
ORDER BY [Order Year]

为此，我们使用 SELECT INTO 语句。下面的屏幕截图显示了我们的新表数据。我们使用此 PIVOT 函数将行数据转换为列。

SQL Server PIVOT 运算符示例

此示例将行中的日历年信息转换为单独的列。在这里，名称列将保持不变，订单数量将根据年份进行分离。

SELECT Name,[2011], [2012], [2013], [2014]
FROM (
      SELECT [Name]
           ,[Order Year]
           ,[Order Quantity]
      FROM [PSource]
     ) AS SOURCE
PIVOT 
   ( 
    SUM([Order Quantity]) FOR [Order Year] 
	 IN ([2011], [2012], [2013], [2014])
   ) AS [Result] 
ORDER BY Name
PIVOT 2

到 SOURCE 的 SELECT 查询将首先执行，从表中提取名称、订单年份和订单数量信息。请参阅 SQL Server 中的 SELECT 语句和 SELECT INTO 语句。

接下来，SOURCE 之后的剩余查询将使用聚合函数 SUM 和 PIVOT 将行转换为列。在此查询中，以下语句将显示输出。

SELECT Name,[2011], [2012], [2013], [2014]

如果您想将相同的功能应用于原始数据，则必须使用以下查询。

USE AdventureWorks2014
GO
SELECT Name,[2011], [2012], [2013], [2014]
 FROM 
   (
    SELECT PROD.Name,
           YEAR(OrdHead.OrderDate) as OrderYear,
           SUM(Details.OrderQty) AS OrderQuantity
    FROM Sales.SalesOrderDetail AS Details
     INNER JOIN Production.Product AS PROD 
       ON Details.ProductID = PROD.ProductID
     INNER JOIN Sales.SalesOrderHeader AS OrdHead 
       ON Details.SalesOrderID = OrdHead.SalesOrderID
     GROUP BY PROD.Name,YEAR(OrdHead.OrderDate)
    ) AS SOURCE
PIVOT( SUM(OrderQuantity) FOR OrderYear 
        IN ([2011], [2012], [2013], [2014])
       ) AS Result
ORDER BY Name

PIVOT 方法 2

假设您从一个表中获取所有源信息。如果数据不包含任何 连接 和 分组，请在源表上使用以下查询。它是方法 1 的简短版本。

SELECT Name,[2011], [2012], [2013], [2014]
FROM [PSource]
PIVOT 
   ( 
    SUM([Order Quantity]) FOR [Order Year] 
      IN ([2011], [2012], [2013], [2014])
   ) AS [Result] 
ORDER BY Name
2nd Approach 3

SQL Server PIVOT 替代方案

此替代查询示例纯粹用于面试目的。通常，为了测试您在面试中的编码技能，他们可能会问您一个问题：如何在不使用 PIVOT 的情况下将行转换为列？

SELECT 
    Name
   ,SUM(CASE WHEN [Order Year] = 2011 THEN [Order Quantity] ELSE 0 END)AS [2011]
   ,SUM(CASE WHEN [Order Year] = 2012 THEN [Order Quantity] ELSE 0 END)AS [2012]
   ,SUM(CASE WHEN [Order Year] = 2013 THEN [Order Quantity] ELSE 0 END)AS [2013]
   ,SUM(CASE WHEN [Order Year] = 2014 THEN [Order Quantity] ELSE 0 END)AS [2014]  
FROM [PSource]    
GROUP BY Name
ORDER BY Name
PIVOT Alternative Sum and Case Statements

如果您想将相同的功能应用于原始数据，则必须使用以下查询。

USE AdventureWorks2014
GO
SELECT 
   PROD.Name
  ,SUM(CASE WHEN YEAR(OrdHead.OrderDate) = 2011 THEN (Details.OrderQty) ELSE 0 END)AS [2011]
  ,SUM(CASE WHEN YEAR(OrdHead.OrderDate) = 2012 THEN (Details.OrderQty) ELSE 0 END)AS [2012]
  ,SUM(CASE WHEN YEAR(OrdHead.OrderDate) = 2013 THEN (Details.OrderQty) ELSE 0 END)AS [2013]
  ,SUM(CASE WHEN YEAR(OrdHead.OrderDate) = 2014 THEN (Details.OrderQty) ELSE 0 END)AS [2014]
FROM Sales.SalesOrderDetail AS Details
  INNER JOIN Production.Product AS PROD 
    ON Details.ProductID = PROD.ProductID
  INNER JOIN Sales.SalesOrderHeader AS OrdHead 
    ON Details.SalesOrderID = OrdHead.SalesOrderID
GROUP BY PROD.Name
ORDER BY Name

评论已关闭。