Split comma separated string to multiple rows

สำหรับใครที่เคยใช้ฟังก์ชั่น GROUP_CONCAT() ใน  MySQL มาก่อนก็พอจะเดาผลลัพธ์จากฟังก์ชั่นนี้ได้ว่า ผลลัพธ์จะเป็นค่าตามคอลัมภ์ที่ถูกกรุ๊ป (GROUP BY) และนำมาต่อกันด้วยเครื่องหมายที่ระบุ ปกติค่าดีฟอลต์จะเป็น comma ‘,’ รูปแบบคำสั่งก็จะประมาณนี้

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

ตัวอย่าง


**ภาพจาก mysqltutorial

ข้างบนนี่คือต้นเหตุ มักจะมีกรณีที่เราได้ผลลัพธ์มาแล้ว นั่นคือ “A,B,C” และเราต้องการแยกข้อความที่ได้มาออกเป็นแต่ละแถว (ตาราง t ก่อนที่จะผ่านฟังก์ชั่น GROUP_CONCAT() นั่นแหล่ะ) ดูตัวอย่างกัน

CREATE TABLE DEMO (
    ID INTEGER PRIMARY KEY AUTO_INCREMENT,
    PID VARCHAR(20) NOT NULL,
    ICD10LIST VARCHAR(255) DEFAULT NULL
);
 
INSERT INTO DEMO(ID, PID, ICD10LIST) VALUES(NULL, '101', 'E119,E112,I10'), (NULL, '102', 'E119,E112'), (NULL, '103', 'E119,I10');

ข้อมูลในตารางเป็นแบบนี้ (คุ้น ๆ กันไหม 5555)

**แนวคิดของคำสั่งก็คือทำการสร้างตารางค่า Index ที่อยู่ในชุด/เซตข้อความเพื่อแยกแต่ละไอเท็มออกมา

SELECT
  DEMO.ID,
  DEMO.PID,
  SUBSTRING_INDEX(SUBSTRING_INDEX(DEMO.ICD10LIST, ',', numbers.n), ',', -1) AS ICD10
FROM
  (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) numbers INNER JOIN DEMO
  ON CHAR_LENGTH(DEMO.ICD10LIST)-CHAR_LENGTH(REPLACE(DEMO.ICD10LIST, ',', '')) >= numbers.n-1
ORDER BY
  PID, n

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

จบปิ๊ง !!!

ป.ล.

  • ตาราง numbers จะสร้างไว้ก่อนก็ได้ ปกติก็ใช้บ่อย ๆ นะครับจะด้วย RECURSIVE CTE ก็ได้หรือตัวนี้
  • ตัว Split String จะทำเป็นฟังก์ชั่นไว้ก็ได้นะ ^_^
  • ข้อมูลเป็นข้อมูลสมมุติจาก Data Exchange สมมุติแห่งหนึ่ง