[SQL] PIVOT 和 UNPIVOT

• 說明：

產生ㄧ個2維PIVOT來幫助自己，免的每次要用它還要重新理解ㄧ次。

• 簡介：

PIVOT 將資料行內資料的唯一值旋轉成多個資料行，並對數值型欄位進行彙總（直轉橫）。
UNPIVOT則是跟PIVOT相反（橫轉直）。PS：資料行（Column）、資料列 （Row）。

-- 1.1 利用個人出勤表來進行說明。
-- 1.2 假別：事假、病假、公假和陪產假。
-- 1.3 #PIVOT 用來說明 PIVOT；#UNPIVOT 用來說明 UNPIVOT。
-- 1.4 備註：#PIVOT 有兩筆時數為NULL的資料；
--           #UNPIVOT AAAAA 員工陪產假為NULL、BBBBB 員工公假為0。
CREATE TABLE #PIVOT (Employee char(8),Date datetime,Kind char(10),Hours numeric(4,2))
INSERT INTO #PIVOT VALUES ('AAAAA','20101001','事假',3.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101002','事假',8.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101003','事假',4.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101010','病假',8.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101020','病假',1.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101025','公假',8.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101026','公假',8.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101027','公假',8.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101027','公假',NULL)
INSERT INTO #PIVOT VALUES ('BBBBB','20101001','事假',6.0)
INSERT INTO #PIVOT VALUES ('BBBBB','20101009','病假',8.0)
INSERT INTO #PIVOT VALUES ('BBBBB','20101019','病假',8.0)
INSERT INTO #PIVOT VALUES ('BBBBB','20101019','病假',NULL)
INSERT INTO #PIVOT VALUES ('BBBBB','20101025','陪產假',8.0)
INSERT INTO #PIVOT VALUES ('BBBBB','20101026','陪產假',8.0)
INSERT INTO #PIVOT VALUES ('BBBBB','20101027','陪產假',8.0)
 
CREATE TABLE #UNPIVOT (Employee char(8),事假 numeric(4,2),病假 numeric(4,2),公假 numeric(4,2),陪產假 numeric(4,2))
INSERT INTO #UNPIVOT VALUES('AAAAA',15,9,24,NULL)
INSERT INTO #UNPIVOT VALUES('BBBBB',6,16,0,24)

• PIVOT 圖解

• PIVOT 架構

SELECT GroupCol,[PivotCol資料1],[PivotCol資料2] -- 根據轉置欄位資料產生欄位
    FROM 
        (
          SELECT GroupCol,AggregationCol,PivotCol FROM SourceTable
          -- 2.1 欲進行轉置的資料來源
          -- 2.2 GroupCol（群組欄位） == Employee欄位
          --     AggregationCol（彙總欄位） == Hours欄位
          --     PivotCol（轉置欄位） == Kind欄位
        ) AS p -- 別名不可省略
    PIVOT
        (
          Aggregation(AggregationCol) FOR PivotCol IN ([PivotCol資料1],[PivotCol資料2]......)
          -- 2.3 轉置後欄位（PivotCol資料1,PivotCol資料2），ㄧ定要用[]包起來，不可以用''
          -- 2.4 PivotCol經轉置後，欄位即消失（改用PivotCol資料1,PivotCol資料2來呈現）         
        ) AS pt -- 別名不可省略

• PIVOT T-SQL 語法說明

SELECT *
    FROM 
        (
          SELECT Employee,Kind,Hours 
            FROM #PIVOT
        ) AS p
    PIVOT
        (
          SUM(Hours) FOR Kind IN ([事假],[病假],[公假],[陪產假])
        ) AS pt

-- OR
SELECT *
    FROM 
        (
          SELECT Employee,Kind,SUM(Hours) AS Hours
            FROM #PIVOT
            GROUP BY Employee,Kind
        ) AS p
    PIVOT
        (
          SUM(Hours) FOR Kind IN ([事假],[病假],[公假],[陪產假])
        ) AS pt
 
 -- 彙總函數搭配PIVOT，計算彙總欄位時不會考慮值為NULL的資料

• PIVOT 執行後結果

• UNPIVOT 圖解

• UNPIVOT 架構

SELECT *
    FROM
        (
          Select GroupCol,col2,col3,col4........ FROM SourceTable
          -- 3.1 欲進行反轉置的資料來源
        ) AS p
    UNPIVOT
        (
          ValueCol FOR unPivotCol IN (col2,col3,col4.........) -- ㄧ定要不包含群組欄位
          -- 3.2 ValueCol 和 unPivotCol 為使用者自行命名的欄位名稱
          -- 3.3 ValueCol 的資料為資料列（Row）資料轉成資料行（Column）資料（非群組欄位轉為ValueCol）
          -- 3.4 unPivotCol 的資料為 col2欄位資料、col3欄位資料、col4欄位資料........
        ) AS pv  

• UNPIVOT T-SQL 語法說明

SELECT *
    FROM
        (
          Select Employee,事假,病假,公假,陪產假 FROM #UNPIVOT
        ) AS p
    UNPIVOT
        (
          Hours FOR Kind IN (事假,病假,公假,陪產假)
        ) AS pv

-- OR

SELECT *
    FROM #UNPIVOT AS p
    UNPIVOT
        (
          Hours FOR Kind IN (事假,病假,公假,陪產假)
        ) AS pv

-- AAAAA的陪產假為NULL，UNPIVOT後，AAAAA的假別就不會有陪產假

• UNPIVOT 執行後結果

• PIVOT 和 UNPIVOT 的差異：

PIVOT 和 UNPIVOT 並非完全相反，PIVOT 會執行彙總，因此它會對多個資料列（Row）進行彙總後合併輸出成單一資料列（Col），因為資料列（Row）已經彙總合併成資料行（Col），所以UNPIVOT後並不是原始資料，而是彙總後的資料。

• NULL 的變化

資料行PIVOT成資料列，轉置後欄位會出現NULL。
資料列UNPIVOT成資料行，資料內假如有NULL會消失，並不會呈現。

延伸閱讀
• [SQL] 字串欄位轉置 
• [SQL] Dynamic PIVOT

參考資料

• MSDN PIVOT 和 UNPIVOT、FROM
• An Alternative (Better?) Method to UNPIVOT (SQL Spackle)
• Cross-Tab Report - SQL 2000 的轉置方法
• 論壇討論 - 雙重 PIVOT
• 論壇討論 1、2 - UNPIVOT 搭配 PIVOT