星期六, 11月 27, 2010

[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 1
    • 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 執行後結果
    Pivot 2
    • UNPIVOT 圖解
    Pivot 3
    • 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 4
    • PIVOT 和 UNPIVOT 的差異:
    PIVOT 和 UNPIVOT 並非完全相反,PIVOT 會執行彙總,因此它會對多個資料列(Row)進行彙總後合併輸出成單一資料列(Col),因為資料列(Row)已經彙總合併成資料行(Col),所以UNPIVOT後並不是原始資料,而是彙總後的資料。
    • NULL 的變化
    資料行PIVOT成資料列,轉置後欄位會出現NULL。
    資料列UNPIVOT成資料行,資料內假如有NULL會消失,並不會呈現。
      參考資料

    3 則留言:

    1. 您好,
      請問"Kind IN (事假,病假,公假,陪產假)"
      這部份是否可用 Kind IN (*) 的方式取代?,因為製作時,並不知道有幾種假別。或是有其它方式達成此需求?

      回覆刪除
    2. To Vane ~~
      沒有這樣的用法,請 Google "動態 PIVOT" 就可以找到 T-SQL 解法 ~~

      回覆刪除
    3. 請問一下,以下SQL還有辦法PIVOT嗎?([假單數]/SUM(Hours))
      SELECT Employee,
      --count(Employee) [假單數], --
      Kind, --count(Employee)+'/'+Hours [count_hours]
      CAST (count (Employee) AS VARCHAR)
      + ' / ' +
      CAST (Hours AS VARCHAR)
      [Hours]
      FROM #PIVOT
      GROUP BY Employee, Kind, Hours

      回覆刪除