MySQL สรุปข้อมูลจากแบบแถว เป็นแบบ คอลัมน์ (Pivot Table)
ดูตัวอย่างข้อมูลกันก่อน
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;ผลลัพท์ที่ได้
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.....