Cách gộp một vài hàng bản ghi trong MySQL

Sử dụng CASE WHEN kết hợp MAX và GROUP BY để chuyển hàng thành cột (pivot). Tạo bảng demo68:

mysql> CREATE TABLE demo68 (
    -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> company_name VARCHAR(50),
    -> employee_name VARCHAR(50),
    -> country_name VARCHAR(50)
    -> );

Chèn dữ liệu:

mysql> INSERT INTO demo68(company_name,employee_name,country_name) VALUES('Google','John','US');
mysql> INSERT INTO demo68(company_name,employee_name,country_name) VALUES('Google','Bob','UK');
mysql> INSERT INTO demo68(company_name,employee_name,country_name) VALUES('Google','David','AUS');

Hiển thị dữ liệu:

mysql> SELECT * FROM demo68;

+----+--------------+---------------+--------------+
| id | company_name | employee_name | country_name |
+----+--------------+---------------+--------------+
| 1  | Google       | John          | US           |
| 2  | Google       | Bob           | UK           |
| 3  | Google       | David         | AUS          |
+----+--------------+---------------+--------------+

Truy vấn gộp hàng theo company_name:

mysql> SELECT
    -> company_name,
    -> MAX(CASE WHEN country_name = 'US' THEN employee_name END) AS US_Employee_Name,
    -> MAX(CASE WHEN country_name = 'UK' THEN employee_name END) AS UK_Employee_Name,
    -> MAX(CASE WHEN country_name = 'AUS' THEN employee_name END) AS AUS_Employee_Name
    -> FROM demo68
    -> GROUP BY company_name;

Kết quả:

+--------------+------------------+------------------+-------------------+
| company_name | US_Employee_Name | UK_Employee_Name | AUS_Employee_Name |
+--------------+------------------+------------------+-------------------+
| Google       | John             | Bob              | David             |
+--------------+------------------+------------------+-------------------+