透视转换

从这一篇开始要总结的是透视和逆透视，那么什么是透视和逆透视呢？透视是将数据从行的状态转换成列的状态，而逆透视则是将数据从列的状态转换成行的状态。它们一般应用在生成报表的场景中。

每个透视转换都会涉及三个逻辑处理阶段，每个阶段都有相关元素；分组阶段处理相关的分组或行元素，扩展(Spreading)阶段处理相关的扩展或列元素，聚合阶段处理相关的聚合元素和聚合函数。

下面是一个关于透视转换的示例。

USE tempdb;
GO

-- 透视转换
-- 准备测试数据
IF OBJECT_ID('dbo.Orders','U') IS NOT NULL DROP TABLE dbo.Orders;
GO

CREATE TABLE dbo.Orders
(
    orderid INT NOT NULL,
    orderdate DATETIME NOT NULL,
    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;

得到原始行的状态，如下图：

实现透视转换有两种解决方案，一是使用标准的SQL，另一种是使用PIVOT运算符进行转换。下面是示例代码。

-- 1，使用标准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;

-- 2，PIVOT运算符
SELECT empid,A,B,C,D
FROM (SELECT empid,custid,qty FROM dbo.Orders) AS D
PIVOT(SUM(D.qty) FOR D.custid IN (A,B,C,D)) AS P;

透视转换后的效果如下图。