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

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

USE tempdb;GO-- 逆透视转换(列转行)-- 准备测试数据IF OBJECT_ID('dbo.Orders','U') IS NOT NULL DROP TABLE dbo.Orders;GOCREATE 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;GOSELECT empid,A,B,C,DINTO dbo.EmpCustOrdersFROM (SELECT empid,custid,qty FROM dbo.Orders) AS DPIVOT(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 DWHERE qty IS NOT NULL;-- 2,使用UNPIVOT运算符SELECT empid,custid,qty FROM dbo.EmpCustOrdersUNPIVOT (qty FOR custid IN (A,B,C,D)) AS U;

逆透视后的效果如下图。