~楓花雪岳~: [SQL] Dynamic PIVOT

星期五, 5月 24, 2013

[SQL] Dynamic PIVOT

在 [SQL] PIVOT 和 UNPIVOT 內有人詢問，在不確定轉置選項的情況下，能否使用 PIVOT 來滿足轉置需求。

利用動態組字串方式來達到此需求。

T-SQL 語法

CREATE TABLE #PIVOT (Employee char(8),Date datetime,Kind varchar(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)

DECLARE 
  @pivotcol nvarchar (4000) , -- 轉置欄位變數
  @selectCol nvarchar(4000) , -- PIVOT 後要把 NULL 資料轉為 0 用
  @tsql1 nvarchar(4000) ,     -- 動態組出的 T-SQL 語法（資料 NULL 呈現）
  @tsql2 nvarchar(4000)       -- 動態組出的 T-SQL 語法（資料 0 呈現）

-- 把 Kind 資料塞進 @pivotcol 和 @selectcol 變數內
SELECT 
  @pivotcol = ISNULL(@pivotcol + ' , ' , '') + QUOTENAME(Kind) ,
  @selectcol = ISNULL(@selectcol + ' , ' , '') + 'ISNULL(' + QUOTENAME(Kind) + ',0) AS ' + QUOTENAME(Kind)
FROM #PIVOT
GROUP BY Kind

 -- 方法一：資料 NULL 呈現
SET @tsql1 = 
  '
    SELECT *
    FROM 
      (
        SELECT Employee,Kind,Hours
        FROM #PIVOT
      ) AS P
    PIVOT
      (
        SUM(Hours) FOR Kind IN (' + @pivotcol + ')
      ) AS PV
  '

-- 方法二：資料 0 呈現
SET @tsql2 = 
  '
    SELECT Employee , ' + @selectcol + '
    FROM 
      (
        SELECT Employee,Kind,Hours
        FROM #PIVOT
      ) AS P
    PIVOT
      (
        SUM(Hours) FOR Kind IN (' + @pivotcol + ')
      ) AS PV
  '
EXEC sp_executesql @tsql1
EXEC sp_executesql @tsql2

簡易分析

@pivotcol = ISNULL(@pivotcol + ' , ' , '') + QUOTENAME(Kind)

這段語法是利用 NULL 特性搭配 ISNULL() 來對字串進行整理，當處理第一筆資料時，@pivotcol 為 NULL，NULL 加逗號還是 NULL，因此 ISNULL() 判斷會跳到第二個參數去，也就不會加入逗號，當第二筆資料加入 @pivotcol 時，因為 @pivotcol 已經有第一筆資料，非 NULL 情況，因此會加上逗號，此作法可以簡化串接字串結束後，又要特地去處理多餘的逗號（可能在開頭或結尾，看 T-SQL 語法如何下），@selectcol 的邏輯也是同樣的。