透视转换

透视数据（Pivoting）是一种把数据从行的状态旋转为列的状态的处理，在这个过程可能需要多值进行聚合。

生成实例数据库

USE tempdb;

IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;
GO

CREATE TABLE dbo.Orders
(
  orderid   INT        NOT NULL,
  orderdate DATE       NOT NULL, -- prior to SQL Server 2008 use DATETIME
  empid     INT        NOT NULL,
  custid    VARCHAR(5) NOT NULL,
  qty       INT        NOT NULL,
  CONSTRAINT PK_Orders PRIMARY KEY(orderid)
);

INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES
  (30001, '20070802', 3, 'A', 10),
  (10001, '20071224', 2, 'A', 12),
  (10005, '20071224', 1, 'B', 20),
  (40001, '20080109', 2, 'A', 40),
  (10006, '20080118', 1, 'C', 14),
  (20001, '20080212', 2, 'B', 12),
  (40005, '20090212', 3, 'A', 10),
  (20002, '20090216', 1, 'C', 20),
  (30003, '20090418', 2, 'B', 15),
  (30004, '20070418', 3, 'C', 22),
  (30007, '20090907', 3, 'D', 30);

 

常规查询

SELECT empid, custid, SUM(qty) AS sumqty
FROM dbo.Orders
GROUP BY empid, custid;

 

透视转换后的结果

透视转换将涉及三个逻辑处理阶段：分组，扩展以及聚合。分组阶段处理相关的分组或行元素，扩展阶段处理相关的扩展或列元素，聚合阶段处理相关的聚合元素和聚合函数。

例子中需要在结果中为每个唯一的雇员ID生成一行记录，这就需要对Orders表中的行按照其empid进行分组，因此分组元素是empid。

Orders表分别用一个列保存所有客户ID值和他们的订货量。透视处理为每个唯一的客户ID生成一个不同的结果列，用于保存该客户的聚合订货量。可以将这个处理看作是根据客户ID来扩展的聚合订货量，即custid。

最后由于透视转换设计分组，所以需要对数据进行聚合，以生成分组元素和扩展元素的交叉位置上的结果只。所以聚合函数为SUM，聚合元素为qty。

 

标准的SQL 透视转换

SELECT empid,
  SUM(CASE WHEN custid = 'A' THEN qty END) AS A,
  SUM(CASE WHEN custid = 'B' THEN qty END) AS B,
  SUM(CASE WHEN custid = 'C' THEN qty END) AS C,
  SUM(CASE WHEN custid = 'D' THEN qty END) AS D
FROM dbo.Orders
GROUP BY empid;

 

PIVOT运算符

SELECT...
FROM <source_table_or_table_expression>
    PIVOT(<agg_func> (aggregation_element)
            FOR <spreading_element>
                IN(<list_of_target_column>)) AS <result_table_alias>

SELECT empid, A, B, C, D
FROM (SELECT empid, custid, qty
      FROM dbo.Orders) AS D
  PIVOT(SUM(qty) FOR custid IN(A, B, C, D)) AS P;

注意：PIVOT运算不需要为它显示地指定分组元素，也就是不需要在查询中使用GROUP BY子句。PIVOT运算符隐式的源表或者表表达式中既没有指定为扩展元素也没有指定为聚合元素的那些元素作为分组元素。

动态SQL实现透视转换

对于静态查询，必须事先知道在PIVOT运算符的IN子句中应该指定哪些值，可以利用动态SQL来克服这个问题

静态PIVOT

SELECT *
FROM (SELECT shipperid, YEAR(orderdate) AS orderyear, freight
      FROM Sales.Orders) AS D
  PIVOT(SUM(freight) FOR orderyear IN([2006],[2007],[2008])) AS P;

动态PIVOT

DECLARE @orderyears AS NVARCHAR(1000),
        @sql AS NVARCHAR(MAX)
SELECT @orderyears=ISNULL(@orderyears+',','')+QUOTENAME(CAST(YEAR(orderdate) AS NVARCHAR(4))) FROM Sales.Orders GROUP BY YEAR(orderdate)

SET @sql= N'SELECT * FROM
                (SELECT shipperid, YEAR(orderdate) as orderyear, freight FROM Sales.Orders) AS O
                    PIVOT(SUM(freight) FOR orderyear IN ('+@orderyears+N')) AS T'

EXEC sp_executesql @sql