Day: June 26, 2018

  • Transpose rows into columns using the Oracle PIVOT operator

    ก่อนหน้านี้เราเคยนำเสนอบทความวิธีการทรานสโพส (หมุน) ข้อมูลจากแถวเป็นคอลัมน์ ด้วยการใช้ฟังก์ชัน SUM และ DECODE กันมาแล้ววันนี้ลองมาใช้อีกวิธีในการแสดงผลดังกล่าวด้วย PIVOT ก่อนอื่นมาดูในส่วน syntax ของ PIVOT กันก่อน

     

    PIVOT Syntax

    SELECT * FROM
    (
      SELECT column1, column2
      FROM tables
      WHERE conditions
    )
    PIVOT 
    (
      aggregate_function(column2)
      FOR column2
      IN ( expr1, expr2, ... expr_n) | subquery
    )
    ORDER BY expression [ ASC | DESC ];

    โดยที่

    aggregate_function คือ aggregate function เช่น SUM, COUNT, MIN, MAX, หรือ AVG

    IN ( expr1, expr2, … expr_n ) คือ รายการค่าของ column2 ที่ต้องการจะแสดงในส่วนคอลัมภ์

     

    คราวนี้ลองมาดูตัวอย่างกัน 

    สมมติเรามีข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่ซึ่งประกอบด้วย 6 ฟิลด์ข้อมูลแสดงดังตัวอย่างด้านล่าง
    ข้อมูล: ตาราง TEST_NEW_STUDENT เป็นตัวอย่างข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 16 รายการ

    โจทย์ เราต้องการจะแสดงจำนวนของนักศึกษาแยกตามคณะที่สังกัด โดยให้แสดงข้อมูลอยู่ในรูปแบบคอลัมภ์

    ดังนั้นจึงเราสามารถเขียนคิวรีโดยใช้ PIVOT เพื่อแก้ปัญหาข้างต้นได้ดังนี้

    SELECT *
    FROM (SELECT ‘TotalStudent’ fac_summary, fac_id FROM test_new_student)
    PIVOT
    (COUNT (fac_id)
    FOR fac_id
    IN (’06’ Engineering, ’08’ Science, ’12’ Natural_Resources))

     

    ผลลัพธ์ที่ได้ : แสดงข้อมูลจำนวนนักศึกษาซึ่งประกอบด้วย จำนวนนักศึกษาคณะวิศวกรรมศาสตร์ คณะวิทยาศาสตร์ และคณะทรัพยากรธรรมชาติ ตามลำดับ โดยแสดงข้อมูลในรูปแบบคอลัมภ์

  • Oracle : Transpose rows into columns using SUM and DECODE

    วันนี้เราจะนำเสนอวิธีการทรานสโพส (หมุน) ข้อมูลจากแถวเป็นคอลัมน์ โดยการประยุกต์ใช้ฟังก์ชัน SUM และ ฟังก์ชัน DECODE ก่อนอื่นจะขออธิบายในส่วนของฟังก์ชัน SUM และ DECODE กันก่อน

    ฟังก์ชัน SUM

    SUMจัดเป็นฟังก์ชันประเภท Aggregate Function ทำหน้าที่ในการคำนวณผลรวมของค่าในคอลัมภ์ มีรูปแบบการใช้งานดังตัวอย่างข้างล่างนี้

    SELECT SUM(aggregate_expression)
    FROM tables
    [WHERE conditions];

    โดยที่

    aggregate_expression คือ คอลัมภ์ที่ต้องการค่าผลรวม โดยที่ข้อมูลในคอลัมภ์จะต้องเป็นชนิดตัวเลขเท่านั้น

    มาดูตัวอย่างการใช้งานกัน

    SELECT fac_id, SUM (eng_score) sum_eng_score
    FROM test_new_student
    GROUP BY fac_id;

    ผลลัพธ์ที่ได้ : แสดงผลรวมของคะแนนภาษาอังกฤษของแต่ละคณะ

    ฟังก์ชัน DECODE

    DECODE เป็นการเขียนเงื่อนไขบนชุดคำสั่ง select  โดยมีลักษณะการทำงานเหมือน IF-THEN-ELSE โดยถ้าเข้าเงื่อนไขที่กำหนด ต้องการให้ทำอะไรก็ให้ระบุลงไป

    Syntax:

    DECODE( expression , search , result [, search , result]... [, default] )

    โดยที่

    expression คือ ค่าที่จะเอามาใช้เปรียบเทียบเงื่อนไข
    compare_value คือ ค่าที่สามารถจะมาเปรียบเทียบได้กับ expression
    return_value คือค่าที่จะแสดงออกมาเมื่อ ค่าของ expression = compare_value
    default_return_value คือค่าที่จะแสดง เมื่อ expression <> compare_value

     

    ลองมาดูตัวอย่างการใช้งานกัน 

    สมมติเรามีข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่ซึ่งประกอบด้วย 6 ฟิลด์ข้อมูลแสดงดังตัวอย่างด้านล่าง
    ข้อมูล: ตาราง TEST_NEW_STUDENT เป็นตัวอย่างข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 16 รายการ

     

    ตัวอย่างคิวรีในการใช้ฟังก์ชัน DECODE

    SELECT fac_id,
    DECODE (
    fac_id,
    ’06’, ‘คณะวิศวกรรมศาสตร์’,
    ’08’, ‘คณะวิทยาศาสตร์’,
    ’12’, ‘คณะทรัพยากรธรรมชาติ’,’null’)
    fac_name,stud_name_thai,stud_sname_thai,sn_code,eng_score
    FROM test_new_student;

    ผลลัพธ์ที่ได้

    โดยความหมายของด้านบนคือ

    ถ้าค่า fac_id ของตาราง test_new_student เป็น ’06’ ก็ให้แสดง คำว่า ‘คณะวิศวกรรมศาสตร์’

    ถ้าค่า fac_id ของตาราง test_new_student เป็น ’08’ ก็ให้แสดง คำว่า ‘คณะคณะวิทยาศาสตร์’

    ถ้าค่า fac_id ของตาราง test_new_student เป็น ’12’ ก็ให้แสดง คำว่า ‘คณะทรัพยากรธรรมชาติ’

     

    โดยปกติถ้าเราเขียนคิวรีเพื่อนับจำนวนนักศึกษาโดยแยกตามคณะที่สังกัดของนักศึกษา เราสามารถเขียนคิวรีได้ดังนี้

    SELECT fac_id, COUNT (*) fac_summary
    FROM test_new_student
    GROUP BY fac_id;

    ผลลัพธ์ที่ได้ : แสดงจำนวนนักศึกษาแยกตามคณะที่สังกัด การแสดงผลปกติจะแสดงในรูปแบบแถว

     

    คราวนี้ลองมาตั้งโจทย์ใหม่โดยยังมีความต้องการเหมือนเดิมคือ นับจำนวนนักศึกษาโดยแยกตามคณะที่สังกัดของนักศึกษา แต่เปลี่ยนจากการแสดงในรูปแบบแถวให้แสดงในรูปแบบคอลัมภ์แทน

    จากโจทย์เราสามารถประยุกต์ใช้งานฟังก์ชัน sum และ decode เพื่อจะแปลงข้อมูลในรูปแบบแถวให้อยู่ในรูปแบบ
    คอลัมภ์ได้ โดยเราสามารถเขียนคิวรีได้ดังนี้

    SELECT ‘TotalStudent’ faculty,
    SUM (DECODE (fac_id, ’06’, 1)) Engineering,
    SUM (DECODE (fac_id, ’08’, 1)) Science,
    SUM (DECODE (fac_id, ’12’, 1)) Natural_Resources
    FROM test_new_student

    ผลลัพธ์ที่ได้ : แสดงข้อมูลจำนวนนักศึกษาซึ่งประกอบด้วย จำนวนนักศึกษาคณะวิศวกรรมศาสตร์ คณะวิทยาศาสตร์ และคณะทรัพยากรธรรมชาติ ตามลำดับ โดยแสดงข้อมูลในรูปแบบคอลัมภ์