[Challenge] 動態定位的欄位轉置

Beyond Relational TSQL Challenge 42

這個挑戰是關於把資料從 ROW 轉成 Column 的商業邏輯，這個應用程式管理員工工作時數，應用程式必須產生每一個員工工作時數資訊，讓其他應用程式來跑。工作時數被分類為一般工作時數、加班時數、假日時數等。

• 資料來源

EmployeeID PayType PayHours
---------- ------- --------
10001      R       01:30
10001      R       05:15
10001      O       01:00
10001      H       01:30
10002      R       08:00
10002      I       01:00
10002      I       01:30

第一個欄位儲存員工工號。第二個欄位儲存付款類別。R 代表 一般工作時數、H 代表 假日等意義。每一個員工付款類別必須累加。產生的結果，每一個員工必須只有一個 ROW 資料。

• 產生結果

Employee Code1 Pay1 Code2 Pay2 Code3 Pay3 Code4 Pay4
-------- ----- ---- ----- ---- ----- ---- ----- ----
10001    R     6.75 O     1.00 H     1.50       0.00
10002    R     8.00 I     2.50       0.00       0.00

• 規則
1. 有 R、O、I 和 H 四種付款類別。
2. 每一種類別在每一個 ROW 中，必須產生 codeN 和 payN 欄位；codeN 欄位資料必須為付款類別、payN 欄位資料必須為時數。
3. 來源資料表中的資料格式為 HH:MM，MM 這個部分只會是 00、15、30 和 45。
4. 產生結果必須是數值形態， 15 分鐘必須用 0.25、30 分鐘必須用 0.5 來呈現，請以此類推。
5. 每一個付款類別有其權重，權順序為 R、O、I、H，R 為最高、H 為最低。
6. 最後結果必須根據其權重且付款類別必須和時數放在一起。
7. 產生的結果必須根據員工編號來排序。
8. 產生的結果假如是 0 ， 必須為 0.00。

• 個人解法

IF OBJECT_ID('TC42', 'U') IS NOT NULL 
BEGIN
  DROP TABLE TC42 
END
GO

CREATE TABLE TC42
(
  EmployeeID VARCHAR(5),
  PayType CHAR(1),
  PayHours CHAR(5)
)
INSERT INTO TC42 (EmployeeID, PayType, PayHours)
SELECT '10001', 'R', '01:30' UNION ALL
SELECT '10001', 'R', '05:15' UNION ALL
SELECT '10001', 'O', '01:00' UNION ALL
SELECT '10001', 'H', '01:30' UNION ALL
SELECT '10002', 'R', '08:00' UNION ALL
SELECT '10002', 'I', '01:00' UNION ALL
SELECT '10002', 'I', '01:30' 

SELECT 
  T1.EmployeeID,
  ISNULL(MAX(CASE WHEN T1.Weight = 1 THEN T1.PayType ELSE NULL END),'') AS Code1,
  ISNULL(SUM(CASE WHEN T1.Weight = 1 THEN T1.Pay ELSE NULL END),0) AS Pay1,
  ISNULL(MAX(CASE WHEN T1.Weight = 2 THEN T1.PayType ELSE NULL END),'') AS Code2, 
  ISNULL(SUM(CASE WHEN T1.Weight = 2 THEN T1.Pay ELSE NULL END),0) AS Pay2,
  ISNULL(MAX(CASE WHEN T1.Weight = 3 THEN T1.PayType ELSE NULL END),'') AS Code3,
  ISNULL(SUM(CASE WHEN T1.Weight = 3 THEN T1.Pay ELSE NULL END),0) AS Pay3,
  ISNULL(MAX(CASE WHEN T1.Weight = 4 THEN T1.PayType ELSE NULL END),'') AS Code4,
  ISNULL(SUM(CASE WHEN T1.Weight = 4 THEN T1.Pay ELSE NULL END),0) AS Pay4
FROM
  (
    SELECT 
      EmployeeID , PayType ,
      CAST(mm / 60 + 1.0 * (mm % 60) / 60 AS numeric(3,2)) AS Pay,
      Weight
    FROM
      (
        SELECT 
          EmployeeID , PayType , 
          SUM
            (
              CAST(SUBSTRING(PayHours,1,2) AS smallint) * 60 + 
              CAST(SUBSTRING(PayHours,4,2) AS smallint)
            ) AS mm,
          ROW_NUMBER() OVER
            (
              PARTITION BY EmployeeID 
              ORDER BY 
                CASE PayType
                  WHEN 'R' THEN 1
                  WHEN 'O' THEN 2
                  WHEN 'H' THEN 3 
                  WHEN 'I' THEN 4
                  ELSE 0
                END
            ) AS Weight
        FROM TC42
        GROUP BY EmployeeID , PayType
      ) AS T
) AS T1
GROUP BY T1.EmployeeID 
ORDER BY T1.EmployeeID

• 個人解法邏輯：
1. 利用SUBSTRING() 來抓出 PayHours 的時和分，並把時轉成分；利用 ROW_NUMBER() 來產生權重欄位。
2. 把整合過的總分除 60 求得小時、模除（%）60 求得剩下的分再除 60 計算比例。
3. 針對 EmployeeID 進行 GROUP，利用權重欄位來決定欄位順序，CASE WHEN 撘配 MAX() 和 SUM()  來進行轉置。

• 多欄位 PIVOT 應用

看完全部的 Solutions 後，發現些自己的盲點和可以利用多欄位 PIVOT來完成挑戰，把語法改寫成下面。

IF OBJECT_ID('TC42', 'U') IS NOT NULL 
BEGIN
  DROP TABLE TC42 
END
GO

CREATE TABLE TC42
(
  EmployeeID VARCHAR(5),
  PayType CHAR(1),
  PayHours CHAR(5)
)
INSERT INTO TC42 (EmployeeID, PayType, PayHours)
SELECT '10001', 'R', '01:30' UNION ALL
SELECT '10001', 'R', '05:15' UNION ALL
SELECT '10001', 'O', '01:00' UNION ALL
SELECT '10001', 'H', '01:30' UNION ALL
SELECT '10002', 'R', '08:00' UNION ALL
SELECT '10002', 'I', '01:00' UNION ALL
SELECT '10002', 'I', '01:30' 

SELECT 
  EmployeeID , 
  ISNULL(MAX([pay1]),'') AS code1,
  ISNULL(SUM([code1]),0) AS pay1,
  ISNULL(MAX([pay2]),'') AS code2,
  ISNULL(SUM([code2]),0) AS pay2,
  ISNULL(MAX([pay3]),'') AS code3,
  ISNULL(SUM([code3]),0) AS pay3,
  ISNULL(MAX([pay4]),'') AS code4,
  ISNULL(SUM([code4]),0) AS pay4
FROM
  (
    SELECT 
      T.EmployeeID , T.PayType , T.mm , 
      'pay' + CAST(T.Weight AS CHAR(1)) AS Weight , 
      'code' + CAST(T.Weight AS CHAR(1))  AS Weight2
    FROM
      (
        SELECT 
          EmployeeID , PayType , 
          CAST
            (
              SUM
                (
                  CAST(LEFT(PayHours,2) AS smallint) * 60 + 
                  CAST(RIGHT(PayHours,2) AS smallint)
                ) / 60.0       
              AS numeric(3,2)
            ) AS mm,
          ROW_NUMBER() OVER
            (
              PARTITION BY EmployeeID 
              ORDER BY 
                CASE PayType
                  WHEN 'R' THEN 1
                  WHEN 'O' THEN 2
                  WHEN 'H' THEN 3 
                  WHEN 'I' THEN 4
                  ELSE 0
                END
            ) AS Weight
        FROM TC42
        GROUP BY EmployeeID , PayType
    ) AS T
 ) AS P 
PIVOT
  (
    MAX(PayType) FOR Weight IN ([pay1],[pay2],[pay3],[pay4])
  ) AS PV
PIVOT
  (
    SUM(mm) FOR Weight2 IN ([code1],[code2],[code3],[code4])
  ) AS PV2
GROUP BY EmployeeID

• 多欄位 PIVOT 重點

PIVOT 後的欄位名稱不可以重覆，會有 PIVOT 運算子中指定的資料行名稱 "XXX" 與 PIVOT 引數中現有的資料行名稱衝突的錯誤訊息，因此故意進行 PIVOT 前，先產生一樣的欄位資料，並重新命名欄位名稱，轉置前的資料內容如下圖。

PIVOT 的 SourceTable 必須先整理好，多欄位 PIVOT 只進行轉置動作，無法利用彙總函數來進行加總

延伸閱讀
• [SQL] 字串欄位轉置
• [SQL] PIVOT 和 UNPIVOT
• [SQL] 數學運算-除法

參考資料
• TSQL Challenge 42 - Transform rows to columns with dynamic positioning
• Solutions - 利用 LEFT JOIN 合併
• Solutions - 多欄位 PIVOT 應用
• PIVOT on two or more fields in SQL Server
  
• PIVOT Multiple Columns
• UNPIVOT Multiple Columns
• 技術論壇討論