ดึงข้อมูลจาก MySQL และรวมค่าซ้ำด้วย GROUP_CONCAT

ใช้ GROUP_CONCAT() รวมค่าหลายแถวเป็นสตริงเดียว จัดกลุ่มด้วย GROUP BY

mysql> CREATE TABLE DemoTable1561 (
    -> StudentName VARCHAR(20),
    -> Title TEXT
    -> );
Query OK, 0 rows affected (0.60 sec)

mysql> INSERT INTO DemoTable1561 VALUES('Adam', 'Learning Java');
Query OK, 1 row affected (0.18 sec)
mysql> INSERT INTO DemoTable1561 VALUES('Bob', 'Learning C');
Query OK, 1 row affected (0.11 sec)
mysql> INSERT INTO DemoTable1561 VALUES('Adam', 'Learning Spring and Hibernate Framework');
Query OK, 1 row affected (0.22 sec)
mysql> INSERT INTO DemoTable1561 VALUES('Carol', 'Learning MySQL from basic');
Query OK, 1 row affected (0.30 sec)

mysql> SELECT * FROM DemoTable1561;

+-------------+-----------------------------------------+
| StudentName | Title                                   |
+-------------+-----------------------------------------+
| Adam        | Learning Java                           |
| Bob         | Learning C                              |
| Adam        | Learning Spring and Hibernate Framework |
| Carol       | Learning MySQL from basic               |
+-------------+-----------------------------------------+
4 rows in set (0.00 sec)

รวม Title ต่อ StudentName:

mysql> SELECT StudentName, GROUP_CONCAT(Title SEPARATOR ',') AS FormattedOutput
    -> FROM DemoTable1561
    -> GROUP BY StudentName;

+-------------+-------------------------------------------------------+
| StudentName | FormattedOutput                                       |
+-------------+-------------------------------------------------------+
| Adam        | Learning Java,Learning Spring and Hibernate Framework |
| Bob         | Learning C                                            |
| Carol       | Learning MySQL from basic                             |
+-------------+-------------------------------------------------------+
3 rows in set (0.00 sec)

ตัวเลือก: SEPARATOR กำหนดตัวคั่น, ORDER BY ใน GROUP_CONCAT จัดลำดับก่อนรวม, DISTINCT กันค่าซ้ำ