逆透视转换

逆透视转换是一种将数据从列的状态转换成行的状态的一种技术。进行逆透视转换一般要经历三个逻辑处理阶段：生成副本，提取元素和删除不相关的交叉。

下面是一个进行逆透视转换的示例。

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);
    
IF OBJECT_ID('dbo.EmpCustOrders','U') IS NOT NULL DROP TABLE dbo.EmpCustOrders;
GO

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

SELECT * FROM dbo.EmpCustOrders;

逆透视前：

逆透视也有两种解决方案，一是使用标准SQL，另一种是使用UNPIVOT运算符。下面是示例代码。

-- 1,使用标准SQL
-- 交叉联接，派生表
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 (SELECT 'A' AS custid
                        UNION ALL SELECT 'B'
                        UNION ALL SELECT 'C'
                        UNION ALL SELECT 'D') AS Custs) AS D
WHERE qty IS NOT NULL;

-- 2,使用UNPIVOT运算符
SELECT empid,custid,qty 
FROM dbo.EmpCustOrders
UNPIVOT (qty FOR custid IN (A,B,C,D)) AS U;

逆透视后的效果如下图。

转载于:https://www.cnblogs.com/mcgrady/p/4005775.html