取消樞紐轉換

標籤：style   blog   http   color   io   os   使用   ar   for   

取消樞紐轉換是一種將資料從列的狀態轉換成行的狀態的一種技術。進行取消樞紐轉換一般要經曆三個邏輯處理階段：產生副本，提取元素和刪除不相關的交叉。

下面是一個進行取消樞紐轉換的樣本。

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;

取消樞紐後的效果如。

取消樞紐轉換