SQL Server 透视与逆透视转换解析

本篇文章聊聊T-SQL中对数据进行透视转换 (pivoting)、逆透视转换 (unpivoting) 相关技术。 透视转换是把数据从行的状态旋转为列的状态，逆透视转换则是把数据从列的状态旋转为行的状态。

在 tempdb 数据库（用于演示目的）中创建一个示例数据表 Orders， 并为其填充示例数据。

USE tempdb
GO    
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.orders; 
CREATE TABLE dbo.Orders 
(
    orderid INT NOT NULL PRIMARY KEY,
    orderdate DATE NOT NULL,
    empid INT NOT NULL,
    custid VARCHAR(5) NOT NULL,
    qty INT NOT NULL
)
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 * FROM dbo.Orders

 

一.透视转换

透视数据 (pivoting) 是一种把数据从行的状态旋转为列的状态的处理， 在这个过程中可能须要对值进行聚合。先考虑一个需求：生成一个报表，包含每个雇员和客户组合之间的总订货量。用以下简单的查询可以解决这个需求：

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

输出结果如下：

假如现在需要按照下表（表1）所示格式来生产输出结果。

 

表1是对Orders表中的数据进行聚合和透视转换后的视图，用于生成数据的这种视图的技术被称为透视转换。每个透视转换将涉及三个逻辑处理阶段，每个阶段都有相关的元素：分组阶段处理相关的分组或行元素，扩展(spreading)阶段处理相关的扩展或列元素，聚合阶段处理相关的聚合元素和聚合函数．例子中，须要在结果中为每个唯一的雇员ID生成一行记录，这就须要对Orders表中的行按照其empid列进行分组，因此，例子中的分组元素应该是empid列。Orders表分别用一个列来保存所有的客户ID值和他们的订货量，透视处理为每个唯一的客户ID生成一个不同的结果列，用于保存该客户的聚合订货量，所以本例中的扩展元素为custid列。最后，由于透视转换涉及分组，所以需要对数据进行聚合，以生成分组元素和扩展元素的”交叉“位置上的结果值，这就须要标识聚合函数（本例为SUM)和聚合元素（本例为qty列）。

下面将介绍两种透视转换的解决方案：一种是使用标准SQL，一种是使用T-SQL特定的PIVOT运算符。

•    使用标准SOL

透视转换的标准解决方案以一种非常直接的方式来处理转换过程中涉及的三个阶段。分组阶段用GROUP BY子句实现。扩展阶段通过在SELECT子句中为每个目标列指定CASE表达式来实现，这须要事先知道每个扩展元素的取值，并为每个值指定一个单独的CASE表达式。本例须要对4个客户(A、B、C和D)的订货预进行扩展，所以得用4个CASE表达式。例如，以下是为客户A指定的CASE表达式：

CASE WHEN custid ='A' THEN qty END

只有当前行代表客户A的订单时，这个表达式才返回当前行的订货量；否则返回NULL。 如果CASE表达式没有指定ELSE子句，则默认为ELSE NULL。也就是说，在客户A的目标列中，只有与客户A相关联的订货最才会作为列值出现；其他所有情况下的
列值均为NULL。如果事先不知道须要扩展的值（本例中为不同的客户ID)'而且希望从数据中查询这些值，就得使用动态SQL去构建查询字符串，井执行查询。最后，聚合阶段通过为每个CASE表达式的结果应用相关的聚合函数（本例为SUM)来实现。例如，以下表达式为客户A生成结果列：

SUM(CASE WHEN custid ='A' THEN qty END) AS A

以下是对订单数据进行透视转换解决方案的完整查询语句， 返回每个雇员（按行）和客户（按列）的总订货量：

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;

•    使用 T-SOL PIVOT 运算符

PIVOT运算符也是在查询的FROM子句的上下文中执行操作。它对某个源表或表表达式进行操作、透视数据， 再返回一 个结果表 。PIVOT运算符同样涉及前面介绍的三个逻辑处理阶段（分组 、 扩展及聚合）和同样的透视转换元素， 但使用的是不同的SQL Server原生语法。使用PIVOT运算符的查询语法的一般格式为：

SELECT ... 
FROM <source_table_or_table_expression>
PIVOT(<agg_func>(<aggregation_element>)
FOR <Spreading_element>
IN (<list_of_target_columns>)) AS <result_table_alias>

PIVOT运算符不须要为它显式地指定分组元素， 也就不须要在查询中使用GROUP BY子句。 PIVOT运算符隐式地把源表（或表表达式）中既没有指定为扩展元素， 也没有指定为聚合元素的那些元素作为分组元素。 所以在 使用 PIVOT运算符时， 须要保证PIVOT运算符的源表除了分组、扩展和聚合元素以外， 不能再包含其他属性（列）。 以便在指定了扩展元素和聚合元素以后， 剩下的属性全部都是将要指定为分组元素的属性。 为此， 一般不直接把PIVOT运算符应用到源表（本例为Orders表），而是将其应用到一个表表达式。

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; 

 

二.逆透视转换

逆透视转换是一种把数据从列的状态旋转为行的状态的技术。 通常，它涉及查询数据的透视状态， 将来自单个记录中多个列的值扩展为单个列中具有相同值的多个记录。 换句话说， 把透视表中的每个源行潜在地转换成多个行， 每行代表源透视表的一个指定的列值。 运行以下代码， 在 tempdb 数据库（用千演示目的）中创建， 并填充 EmpCustOrders 表。

IF OBJECT_ID('dbo.Empcustorders','U') IS NOT NULL 
DROP TABLE dbo.Empcustorders; 
SELECT empid, A, B, C, D 
INTO dbo.Empcustorders 
FROM (SELECT empid, custid, qty 
        FROM dbo.Orders) AS D 
PIVOT(SUM(qty) FOR custid IN(A, B, C, D)) AS P;
SELECT * FROM dbo.Empcustorders;

在这个表中，每行代表一个雇员，每列分别代表4个客户A、B、C和D中的一位，而雇员行和客户列的交叉位置则是每对雇员和客户之间的订货量。注意，不相关的交叉（无订单活动的雇员一客户组合）用NULL代表。现在要求逆透视转换数据，为每个雇员和客户组合返回一行记录，其中包含这一组合的订货量。

逆透视也有两种解决方案：一种是使用标准SQL，一种是使用T-SQL特定的UNPIVOT运算符。

•    使用标准SOL

标准SQL解决方案非常明确地要实现3个逻辑处理阶段：生成副本、提取元素和删除不相关的交叉。第一步是根据来源表的每一行生成多个副本（为需要逆透视的每个列生成一 个副本）。在这个例子中，需要为A、B、C及DC代表客户ID)4个列分别生成一个副本。在关系代数和SQL中，可以用笛卡尔积（交叉联接）来生成每一行的多个副本。为此，须要在EmpCustOrders表和一个每行代表一个客户的表之间进行交叉联接。从SQLServer 2008开始，可以使用表值构造函数，按照VALUES子句的格式来创建一个虚拟表，该表中每个客户对应一行记录。第一步处理的查询语句如下所示：

 

SELECT* 
FROM dbo.EmpCustOrders 
CROSS JOIN (VALUES('A'),('B'),('C'),('D')) AS Custs(custid); 

 

第二步是生成一个数据列（本例中称这个列为 qty)， 由它返回与当前副本所代表的客户相对应的列值。 具体到本例而言， 如果当前 custid 的值为A, 则 qty 列应该返回A列的值；如果当前custid的值为B,则qty列应该返回B列的值，以此类推。用一个简单的CASE表达式就可以实现这一步，如下所示：

SELECT empid,custid, 
    CASE custid
      WHEN 'A' THEN A
      WHEN 'B' THEN B
      WHEN 'C' THEN C
      WHEN 'D' THEN D
    END AS qty
FROM dbo.EmpCustOrders 
CROSS JOIN (VALUES('A'),('B'),('C'),('D')) AS Custs(custid); 

在原始表中，NULL值代表不相关的交叉。为了删除不相关的交叉，可以在实现步骤二的查询的基础上定义一个表表达式，在外部查询中过滤掉NULL值。

SELECT * 
FROM(SELECT empid,custid, 
        CASE custid
          WHEN 'A' THEN A
          WHEN 'B' THEN B
          WHEN 'C' THEN C
          WHEN 'D' THEN D
        END AS qty
    FROM dbo.EmpCustOrders 
    CROSS JOIN (VALUES('A'),('B'),('C'),('D')) AS Custs(custid)) AS D
WHERE qty IS NOT NULL  

•    使用 T-SOL UNPIVOT 运算符

对数据进行逆透视转换时，会为源表中想要进行逆透视的任意列生成两个结果列。 在这个例子中，须要对源表列A、B、C和D进行逆透视， 为它们生成两个结果列custid和 qty, 前者用千保存源表列的名称("A"、 "B"、 "C"及"D"), 后者用千保存源表列的值
（本例为订货量）。 为了方便地进行逆透视转换，SQLServer 2005引入了一个非常优雅的、 极为精简的原生表运算符一UNPIVOT。 使用UNPIVOT运算符的查询语句的一般格式为：

 

SELECT ... 
FROM <source_table_or_table_expression>
  UNPIVOT(<target_col_to_hold_source_col_values>
    FOR <target_col_to_hold_source_col_names> IN(<list_of_source_columns>)) AS
<result_table_alias>
...;

 

与PIVOT运算符类似，UNPIVOT也是作为表运算符，在FROM子句的上下文中执行操作。它的操作对象是源表或表表达式（例如， 本例的EmpCustOrders)。在UNPIVOT运算符的圆括号中须要指定的内容包括： 用千保存源表列值的目标列名（这里为qty)， 用于保存源表列名的目标列名(custid), 以及源表的列名列表(A、B、C,D)。在UNPIVOT运算符的圆括号后面， 可以为表运算符的结果表提供一个别名。

SELECT empid, custid, qty
FROM dbo.Empcustorders
UNPIVOT(qty FOR custid IN(A, B, C, D)) AS U;

 

三.后记

对经过透视转换所得的表再进行逆透视转换，并不能得到原来的表。因为逆透视转换只是把经过透视转换后的值再旋转到另一种新的格式。但是，经过逆透视转换后的表可以再通过透视转换回到原来的状态。换句话说，透视转换中的聚合操作会丢失掉源表中的详细信息。经过透视转换后，保存下来的只是操作之间的所有聚合结果，而 逆透视转换则不会丢失任何信息。

 

 

好了，本篇文章就介绍到这儿，欢迎大家留言交流；喜欢或有帮助到您的话，点个赞或推荐支持一下！

 

转载于:https://www.cnblogs.com/johnvwan/p/9488921.html