unpivot詳解

一、unpivot函數

SELECT *
FROM (
        SELECT id, name, [2019-Q1], [2019-Q2], [2019-Q3], [2019-Q4]
        FROM unpivot_test
    ) p
UNPIVOT (
        amount
        FOR quarter IN ([2019-Q1], [2019-Q2], [2019-Q3], [2019-Q4])
    ) AS unpvt;

大家都知道Pivot可以將多行轉化為一行，而把一行數據轉化為多行數據，可以用到SQL SERVER資料庫提供的Unpivot函數。Unpivot函數是一種數據變換方法，特別適用於將水平的列數據轉換為垂直的行數據。

Unpivot函數有兩個必須的信息：1是要進行Unpivot操作的列名，2是對應列名的數據值所需要創建的目標列，Unpivot SQL語句的語法如下：

SELECT unpivot的目標列, unpivot的參照列, unpivot的值列
FROM unpivot的數據源
UNPIVOT
( unpivot的值列 for unpivot的參照列 in 
     (
         unpivot的數據源的列1, 
         unpivot的數據源的列2,
         …………
     )
) as unpivot別名

二、unpivot函數用法

Unpivot函數中，unpivot的目標列、參照列和值列都是需要我們自己定義的。假設我們有一個極簡主義的銷售數據表，結構如下：

CREATE TABLE sales (
    region   VARCHAR(20),
    quarter1 FLOAT,
    quarter2 FLOAT,
    quarter3 FLOAT,
    quarter4 FLOAT
);

INSERT INTO sales (region, quarter1, quarter2, quarter3, quarter4)
VALUES ('North', 1000, 1200, 1300, 1100);
INSERT INTO sales (region, quarter1, quarter2, quarter3, quarter4) 
VALUES ('South', 1200, 1300, 1150, 900);

該數據表有一個區域和四個季度的銷售數據。如果我們要將這個表中水平的四個季度的銷售數據轉化為垂直的行數據（unpivot），也即返回一個區域一列、季度一列和銷售額一列的表格。可以使用以下SQL語句：

SELECT
    region,
    quarter, 
    sales
FROM
    sales
UNPIVOT
    (
        sales FOR quarter IN 
            (quarter1, quarter2, quarter3, quarter4)
    ) AS unpvt;

三、unpivot用法

在某些情況下，需要將列轉換成行。例如，有一個表格，適用於數據倉庫技術，其中提供了5種類型的產品，如下所示：

IF OBJECT_ID('tempdb..#product') IS NOT NULL
BEGIN
    DROP TABLE #product
END
GO
CREATE TABLE #product (
    ProductID INT,
    ProductName VARCHAR(50),
    Category1 INT,
    Category2 INT,
    Category3 INT,
    Category4 INT,
    Category5 INT
)
INSERT INTO #product VALUES
(1, 'TV',   12, 13, 14, 6, 8),
(2, 'Phone', 16, 17, 18, NULL, 19),
(3, 'Radio', 21, NULL, 23, 25, 22)

這種數據格式並不便於選擇、過濾和處理數據，如果需要將列轉換成行，可以使用unpivot函數。unpivot函數的語法是在需要轉換的列名後面編寫目標列的名稱，通過IN引用需要對轉換列進行交叉引用的列名或枚舉值，可能還需要WHERE子句過濾為空列採用如下方式執行：

SELECT *
FROM #product p
UNPIVOT
    (
        value FOR Category IN 
            (Category1, Category2, Category3, Category4, Category5)
    ) AS unpvt
WHERE value IS NOT NULL
ORDER BY ProductID, Category

四、unpivot怎麼讀

Unpivot這個單詞在英語中是「反轉某事物」的意思，也就是把某件事物原本的形態調轉過來再展示。在技術領域中，如果想使用unpivot，就需要明白什麼樣的情況適用於Unpivot函數。

五、unpivot是什麼

在SQL伺服器中，我們可以使用Unpivot函數將數據由列格式轉化為行格式。這個轉換可以通過簡單地調用系統定義函數操作來實現，為了對Unpivot有一個更好的理解，在此提供一個簡短的例子：

SELECT 
       TerritoryID , SalesYear , SalesQuarter , SalesAmount
FROM 
(
    SELECT 
            *
    FROM 
        Sales
) p
UNPIVOT 
(
     SalesAmount FOR SalesQuarter IN ([Q1], [Q2], [Q3], [Q4])
)AS unpvt

六、unpivot 如何使用

下面通過幾個例子更詳細地解釋如何使用Unpivot函數：

例子1：將列旋轉成行

    SELECT C.Code , M.Month , S.Value
    FROM   Sales AS T
    UNPIVOT 
    (
        Value FOR Month IN ([Jan], [Feb], [Mar], [Apr], [May], [Jun],
                            [Jul], [Aug], [Sep], [Oct], [Nov], [Dec])
    ) AS S
    INNER JOIN Customers AS C ON T.CustomerID = C.CustomerID
    INNER JOIN Months AS M   ON M.ID = S.Month
    WHERE T.SalesYear = 2019

假設我們的銷售表中為每年的銷售額指定了12個月份，如上所示。

例子2：需要將一個表連接到另一個表並將其轉換為 UNPIVOT 使用的形式。在進行 Unpivot 操作前，將 Union 操作的結果作為一個表返回：

    SELECT Lower(UPPER(Col1),Col2)AS ColName,Value
    FROM T1
    UNPIVOT 
    (
        Value FOR Col2 IN (Col2a,Col2b,Col2c,Col2d,Col2e)
    ) AS u1;

例子3：Unpivot的目標列、參照列和值列都是需要我們自己定義的。

    SELECT
        region,
        quarter, 
        sales
    FROM
        sales
    UNPIVOT
        (
            sales FOR quarter IN 
                (quarter1, quarter2, quarter3, quarter4)
        ) AS unpvt;

七、unpivot函數使用

在使用Unpivot時需要注意以下幾點：

Unpivot只適用於關係型資料庫
Unpivot只適用於兩個及以上列，對於只含有一列的表也沒有進行任何變化的必要
在Unpivot時目標列名和聯合Unpivot的列寬度應該一致，否則可能會變成空值或者無效值

八、unpivot與pivot區別

Pivot和Unpivot操作是互逆的。 Pivot 操作可以將行和列互換，而 Unpivot 操作則同時涉及列值和列名。

在 SQL language 中，Pivot 查詢可以將多個列欄位值透視為單個結果行，而通常情況下，Unpivot 查詢可以做相反，將一個用於類別分組的列分割為多個行

原創文章，作者：小藍，如若轉載，請註明出處：https://www.506064.com/zh-tw/n/195882.html