原始資料
|
希望呈現
|
|||||||||||||||||||||||||||||||||||||||||||||||||
註: 每名學生將設定兩位緊急聯絡人
(姓名及電話兩種資料內容)
|
|
方法1: 應用笛卡兒積（Cartesian product）
應用笛卡兒積（Cartesian product）方法，首先將資料筆數依轉置欄位數量(緊急聯絡人為2組)倍數成長，由於僅2組，ORACLE直接使用CSV字串拆解以產生2筆資料列(Row)，將第一筆（COLUMN_VALUE=1）資料定義並反轉置第一緊急聯絡人，第二筆則為第二緊急聯絡人；MSSQL則是採用master.dbo.spt_values系統資料表可得0~2047數值序列，將使用0、1兩筆為反轉置對應用。SQL如下。
ORACLE
|
MSSQL
|
SELECT P.STUDENT
, COLUMN_VALUE "#"
, CASE COLUMN_VALUE
WHEN '1' THEN Contact01
WHEN '2' THEN Contact02
END Cont_Psn
, CASE COLUMN_VALUE
WHEN '1' THEN Phone01
WHEN '2' THEN Phone02
END Phone
FROM Phone P
,
TABLE(SYS.DBMS_DEBUG_VC2COLL( 1, 2 ))
|
SELECT P.STUDENT
, number+1
"#"
, CASE number
WHEN 0 THEN Contact01
WHEN 1 THEN Contact02
END Cont_Psn
, CASE number
WHEN 0 THEN Phone01
WHEN 1 THEN Phone02
END Phone
FROM Phone P
, master.dbo.spt_values N
WHERE 1=1
AND N.type ='P'
AND N.number<2
|
方法2: UNPIVOT指令
UNPIVOT為資料庫所提供之反轉置指令，對於一組資料內容轉時相當方便，先前《資料反轉置(UNPIVOT)》案例中，除轉置欄位資料內容外，原資料表的欄位名稱為亦為重要參考屬性需一併轉入，本範例中資料內容(聯絡人、電話兩組轉置內容)才是重點，欄位名稱僅是提供優先順序，以及連結(JOIN)兩組轉置內容上使用，不然資料會被擴展2倍(具有兩組轉置內容)。SQL如下。
ORACLE
|
MSSQL
|
SELECT STUDENT
, ROW_NUMBER() OVER(PARTITION BY STUD_NAME
ORDER BY Cont_Col) "#"
, Cont_Psn --1a.聯絡人姓名
--, Cont_Col --1b.欄位名稱
, Phone --2a.聯絡電話
--, Phone_Col--2b.欄位名稱
FROM Phone P
UNPIVOT
(
Cont_Psn --1a.聯絡人姓名
FOR Cont_Col --1b.欄位名稱(屬性)
IN (Contact01, Contact02)
) CON
UNPIVOT
(
Phone --2a.聯絡電話
FOR Phone_Col --2b.欄位名稱(屬性)
IN (Phone01, Phone02)
) PH
WHERE 1=1
AND SUBSTR(Cont_Col, -2) --1b.Contact[01]
= SUBSTR(Phone_Col, -2) --2b.Phone[01]
|
SELECT STUDENT
, ROW_NUMBER() OVER(PARTITION BY STUD_NAME
ORDER BY Cont_Col) "#"
, Cont_Psn --1a.聯絡人姓名
, Cont_Col --1b.欄位名稱
, Phone --2a.聯絡電話
--, Phone_Col --2b.欄位名稱
FROM Phone P
UNPIVOT
(
Cont_Psn --1a.聯絡人姓名
FOR Cont_Col --1b.欄位名稱(屬性)
IN (Contact01, Contact02)
) CON
UNPIVOT
(
Phone --2a.聯絡電話
FOR Phone_Col --2b.欄位名稱(屬性)
IN (Phone01, Phone02)
) PH
WHERE 1=1
AND RIGHT(Cont_Col, 2) --1b.Contact[01]
= RIGHT(Phone_Col, 2) --2b.Phone[01]
|
方法3: CROSS APPLY
相對上述兩種方法，CROSS APPLY作法方法相對簡單許多，詳細用法可參考如先前《資料反轉置(UNPIVOT)-應用CROSS APPLY》文章所探討，SQL如下。MSSQL2005開始提供此方法，並可針對CROSS APPLY引用資料集以別名(alias)命名及指定其欄位名稱；ORACLE 12C起提供CROSS APPLY方法，但未援別名及欄位名稱定義。SQL如下。
ORACLE
|
MSSQL
|
SELECT P.STUDENT
, SEQ "#"
, Cont_Psn
, Phone
FROM Phone P
CROSS APPLY
(
SELECT 1 SEQ
,
Contact01 Cont_Psn
, Phone01 Phone
FROM DUAL
UNION ALL
SELECT 2, Contact02, Phone02
FROM DUAL
) --不得使用別名及定義欄位名稱
|
SELECT P.STUDENT
, SEQ
"#"
, Cont_Psn
, Phone
FROM Phone P
CROSS APPLY
(VALUES (1, Contact01, Phone01)
, (2, Contact01, Phone01)
) CON (SEQ, Cont_Psn, Phone)
|
以上列出三種方法，由內容及複雜度來看，UNPIVOT相對複雜度較高(較不易理解)，CROSS APPLY相對直覺且簡單，笛卡兒積（Cartesian
product）亦為不錯方法。對於解決問題方的法選擇上，宜海納百川切尤忌墨守成規，各種方法都有其適用性，望能多加思考找出較為適切之作法。
沒有留言:
張貼留言