How to combine few row records in MySQL?

MySQLMySQLi Database

For this, use CASE WHEN concept. Let us first create a table −

mysql> create table demo68
−> (
−> id int not null auto_increment primary key,
−> company_name varchar(50),
−> employee_name varchar(50),
−> country_name varchar(50)
−> );
Query OK, 0 rows affected (1.86 sec)

Insert some records into the table with the help of insert command −

mysql> insert into demo68(company_name,employee_name,country_name)
values('Google','John','US');
Query OK, 1 row affected (0.29 sec)

mysql> insert into demo68(company_name,employee_name,country_name)
values('Google','Bob','UK');
Query OK, 1 row affected (0.10 sec)

mysql> insert into demo68(company_name,employee_name,country_name)
values('Google','David','AUS');
Query OK, 1 row affected (0.08 sec)

Display records from the table using select statement −

mysql> select *from demo68;

This will produce the following output −

+----+--------------+---------------+--------------+
| id | company_name | employee_name | country_name |
+----+--------------+---------------+--------------+
| 1  | Google       | John          | US           |
| 2  | Google       | Bob           | UK           |
| 3  | Google       | David         | AUS          |
+----+--------------+---------------+--------------+
3 rows in set (0.00 sec)

Following is the query to combine few row records in MySQL −

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;

This will produce the following output −

+--------------+------------------+------------------+-------------------+
| company_name | US_Employee_Name | UK_Employee_Name | AUS_Employee_Name |
+--------------+------------------+------------------+-------------------+
| Google       | John             | Bob              | David             |
+--------------+------------------+------------------+-------------------+
1 row in set (0.05 sec)
raja
Published on 20-Nov-2020 07:26:58
Advertisements