MySQL สรุปข้อมูลจากแบบแถว เป็นแบบ คอลัมน์ (Pivot Table)

ในตอนที่แล้ว (MySQL ออกรายงานข้อมูลรายเดือน) ผมได้อธิบายถึงวิธีสรุปข้อมูลแบบรายเดือน รายปี ไปแล้ว ซึ่งการสรุปข้อมูลแบบนี้ จำนวนคอลัมน์ มันคงที่ คือ 1 - 12 (หรือรายเดือน) ทีนี้หากเราต้องการจะสรุปข้อมูลแบบที่คอลัมน์สามารถเปลี่ยนแปลงได้ล่ะ จะทำยังไงดี

ดูตัวอย่างข้อมูลกันก่อน

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

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(category_name=''',category_name,''',value,0)) AS ',category_name)) INTO @sql FROM categories;

SET @sql = CONCAT('SELECT ', @sql, ' FROM categories');

PREPARE tmp FROM @sql;

EXECUTE tmp;

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

หลังจากรันคำสั่งด้านบนเสร็จจนได้ผลลัพท์ออกมาแล้วต้องสั่งทำลาย object ด้วยคำสั่งนี้เพิมเติมนะครับ
DEALLOCATE PREPARE tmp;
อธิบายคำสั่งที่ใช้นะครับ

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(category_name=''',category_name,''',value,0)) AS ',category_name)) INTO @sql FROM categories;

คำสั่งด้านบนเป็นการจองตัวแปร sql และทำการสร้าง Query String ในส่วนที่จะใช้เป็นผลลัพท์ของ Query เราจะได้ผลลัพท์ที่ sql ประมาณนี้

SUM(IF(category_name='หมา',value,0)) AS หมา,

SUM(IF(category_name='หมู',value,0)) AS หมู,

SUM(IF(category_name='เป็ด',value,0)) AS เป็ด,

SUM(IF(category_name='เห็ด',value,0)) AS เห็ด,

SUM(IF(category_name='ไก่',value,0)) AS ไก่

จากนั้นจึงเอาไปใส่คำสั่ง SELECT อีกที ได้ผลลัพท์เป็นคำสั่ง SQL ที่ต้องการ

SELECT

SUM(IF(category_name='หมา',value,0)) AS หมา,

SUM(IF(category_name='หมู',value,0)) AS หมู,

SUM(IF(category_name='เป็ด',value,0)) AS เป็ด,

SUM(IF(category_name='เห็ด',value,0)) AS เห็ด,

SUM(IF(category_name='ไก่',value,0)) AS ไก่

FROM categories

สุดท้าย ประมวลผลคำสั่ง sql ที่ได้ ได้ผลลัพท์ดังตารางที่สองด้านบน

PREPARE tmp FROM @sql;

EXECUTE tmp;

สุดท้ายของสุดท้ายอีกที หลังจากได้ผลลัพท์แล้ว อย่าลืมทำลาย object ด้วย
DEALLOCATE PREPARE tmp;
ข้อจำกัดของสคริปต์นี้ คือ คำสั่ง GROUP_CONCAT ถูกจำกัดความยาวของผลลัพท์ไว้ที่ตัวแปร group_concat_max_len ของ MySQL ซึ่งปกติจะเป็น 1024 ตัวอักษร ดังนั้นหากฐานข้อมูลมีจำนวนคอลัมน์ที่ต้องแสดงผลเป็นจำนวนมาก ค่านี้อาจไม่เพียงพอในการสร้าง Query String ได้ถูกต้อง เราสามารถกำหนดค่านี้ได้เพิ่มเติมก่อนการรันคำสั่งครับ

SET @@group_concat_max_len = 4096;

SET @sql = NULL;

SELECT GROUP_CONCAT.....