การแสดงผลแบบ Transpose ข้อมูลจาก Column มาเป็น Row

โพสต์ครั้งแรก: 28 พฤศจิกายน 2010

ในบางกรณีเราอาจจะต้องการ แสดงผลข้อมูลที่เรียงตัวอยู่เป็นคอลัมน์ให้กลับมาเป็นแถว อย่างเช่นในกรณีที่ เราสร้างตารางที่เป็นเหมือนเทมเพลตเอาไว้เก็บค่าซึ่งจะใช้เป็นอัตราคูณกับค่าในตารางที่แสดงรายการธุรกรรม อย่างเช่นถ้าเรามีตารางเทมเพลต TAX_RATE

SQL> create table tax_rate (country varchar2(30), col1 number, col2 number, col3 number);

Table created

SQL> insert into tax_rate values ('THAILAND',7,3,15);

1 row inserted
SQL> select * from tax_rate;

COUNTRY                    COL1       COL2       COL3
-------------------- ---------- ---------- ----------
THAILAND                      7          3         15

โดยที่เราไม่ได้ระบุชื่อคอลัมน์ไว้ เนื่องจากข้อมูลในแต่ละแถวอาจจะมีความหมายของแต่ละคอลัมน์ต่าง ๆ กัน หรือด้วยเหตุุผลอื่น ๆ

เรามีตารางธุรกรรมที่อ้างถึงอัตราในตารางเทมเพลต TAX_RATE ของเรา โดยการอ้างชื่อคอลัมน์แทนที่จะเป็นชื่อแถว (โดยใช้ Primary Key หรือ Unique Key) เช่น

SQL> create table product_tax (country varchar2(30),product_type varchar2(30),tax_column varchar2(5),price number);

Table created

SQL> insert into product_tax values
('THAILAND','VEHICAL','COL3',100000);

1 row inserted

SQL> insert into product_tax values
('THAILAND','SPIRITS','COL1',300);

1 row inserted

SQL> insert into product_tax values
('THAILAND','MEDICAL','COL2',100);

1 row inserted
SQL> commit;

Commit complete
SQL> select * from product_tax;

COUNTRY             PRODUCT_TYPE            TAX_COLUMN      PRICE
------------------- ----------------------- ---------- ----------
THAILAND            VEHICAL                 COL3           100000
THAILAND            SPIRITS                 COL1              300
THAILAND            MEDICAL                 COL2              100

ถ้าเราต้องการข้อมูลแบบนี้เราจะเขียนคิวรียังไง?

COUNTRY                        PRODUCT_TYPE                   TAX_RATE        PRICE
------------------------------ ------------------------------ ---------- ----------
THAILAND                       VEHICAL                                15     100000
THAILAND                       SPIRITS                                 7        300
THAILAND                       MEDICAL                                 3        100

ประเด็นคือเรารู้ว่าเราสามารถเอา TAX_RATE มา Join กับ PRODUCT_TAX ได้ ถ้า TAX_RATE อยู่ในแนวตั้งในรูปของ TAX_RATE{COUNTRY,COL,RATE}

COUNTRY                        COL              RATE
------------------------------ ---------- ----------
THAILAND                       COL1                7
THAILAND                       COL2                3
THAILAND                       COL3               15

ถ้าการออกแบบตารางมีข้อจำกัดด้วยประการใด ๆ เราอาจจะต้องหาทางกลับเอาข้อมูลที่ยึดกับคอลัมน์มาเป็นแบบแถวแทน (Transpose Column to Row)

ผลคูณคาร์ทีเชียนของ ROWNUM กับการ DECODE

โดยตัวของตารางเองแล้วไม่มีคิวรีใด ๆ ที่สามารถจะจับเอาข้อมูลมากลับด้านเช่นนั้นได้ เราจำเป็นต้องใช้ตารางที่เป็นแนวตั้งมา Join กับมันเพื่อดึงค่าของมันลงมาเป็นแนวแถว เราจะใช้ตารางวิธีการ Select Rownum จากตารางใด ๆ ที่มีจำนวนแถวมากพอกับจำนวนคอลัมน์ที่เราต้องการดึงลงมา เราจะใช้ตาราง ALL_OBJECTS ซึ่งเป็น Data Dictionary View ซึ่งมีอยู่ในทุก ๆ Schema มีจำนวนแถวประมาณสองหมื่นแถวหรือมากกว่า เราเริ่มต้นด้วยคิวรี

SQL> select rownum r from all_objects where rownum <=5;

R
----------
1
2
3
4
5

ROWNUM เป็น Pseudo Column ไม่มีอยู่จริงในตาราง เราสามารถใช้ ROWNUM เพื่อแสดง Running Number ของแต่ละแถวข้อมูลได้

จากนั้นเราจะทำคาร์ทีเชียนระหว่างวิว Select Rownum กับตาราง TAX_RATE โดยใช้ DECODE ในการกำหนดว่าถ้าเป็นแถวที่ 1 ให้แสดงผลจาก COL1, แถวที่ 2 ให้แสดงผลจาก COL2 ฯลฯ

SQL> select t.country,r,decode(r,1,t.col1,2,t.col2,3,t.col3) as rate from
2 tax_rate t,
3 (select rownum r from all_objects where rownum <=5)
4 ;

COUNTRY                                 R       RATE
------------------------------ ---------- ----------
THAILAND                                1          7
THAILAND                                2          3
THAILAND                                3         15
THAILAND                                4
THAILAND                                5

ถ้าเราปรับแต่งนิดนึงโดยการ Concatenate คอลัมน์ 'R' ด้วย String 'COL' เราก็จะสามารถนำคิวรีที่เราสร้างขึ้นมาไป Join กับตาราง PRODUCT_TAX ได้แล้วดังนี้

SQL> select pt.country,pt.product_type,tr.rate,pt.price
2 from
3 product_tax pt,
4 (
5 select t.country,'COL'||r as col,decode(r,1,t.col1,2,t.col2,3,t.col3) as rate from
6 tax_rate t,
7 (select rownum r from all_objects where rownum <=5)
8 ) tr
9 where pt.tax_rate = tr.col
10 /

COUNTRY         PRODUCT_TYPE               RATE      PRICE
--------------- -------------------- ---------- ----------
THAILAND        SPIRITS                       7        300
THAILAND        MEDICAL                       3        100
THAILAND        VEHICAL                      15       1000

ผลคูณคาร์ทีเชียน (Cartesian) ของสองตาราง คือการ Join ตารางสองตารางแบบไม่ใส่ Join Condition (ไม่มี Where Table1.Colx=Table2.Colx) จะให้ผลแบบ 'พบกันหมด' กล่าวคือจำนวนของแถวที่ได้จากการ Join แบบคาร์ทีเชียนจะเท่ากับผลคูณของจำนวนของแถวในแต่ละตาราง เช่น

Table1
COL1
----
1
2
3

TableACOLA 
------
A
B

ถ้าเรา Join ทั้งสองตารางแบบคาร์ทีเชียนจะได้

SQL> select * from table1, tablea;
COL1 COLA
----- -----
1 A
1 B
2 A
2 B
3 A
3 B

โดยปกติผลที่ได้จากการ Join แบบคาร์ทีเชียนไ่ม่ค่อยจะมีความหมายนักในการทำงาน ข้อมูลที่ได้มักจะเป็นขยะ แต่ในบางกรณีก็สามารถนำมาใช้ประโยชน์ได้ เช่นกรณีนี้เป็นต้น

การใส่เงื่อนไขในการแสดงผลด้วย Decode

Decode เป็นฟังก์ชันตัวหนึ่งที่ทำหน้าที่กำหนดเงื่อนไขให้กับการแสดงค่าของคอลัมน์ เช่น DECODE(COL1,1,'ONE',2,'TWO','OTHERS') หมายถึงถ้าค่าในคอลัมน์ COL1 เท่ากับ 1 ก็ให้แสดงผลเป็น 'ONE' ถ้าเป็น 2 แสดงผลเป็น 'TWO' หรือถ้าเป็นตัวอื่น ๆ ก็ให้แสดงผลเป็น 'OTHERS' เป็นต้น