Merge duplicate rows into one row

Question

I have a table like below in MySQL:

+-----+------+-------+-----------+----------+---------+ | #id | NAME | empid | c_lan | Java_lan | Dot_lan | +-----+------+-------+-----------+----------+---------+ | 1 | raju | 111 | yes | NULL | NULL | | 1 | raju | 111 | NULL | NO | NULL | | 1 | raju | 111 | NULL | NULL | na | | 2 | ramu | 222 | yes | NULL | NULL | | 2 | ramu | 222 | NULL | NO | NULL | | 2 | ramu | 222 | NULL | NULL | na | +-----+------+-------+-----------+----------+---------+

How can I write a query to get the below result?

+-----+------+-------+-------+----------+---------+ | #id | name | empid | c_lan | Java_lan | Dot_lan | +-----+------+-------+-------+----------+---------+ | 1 | raju | 111 | yes | no | na | | 2 | ramu | 222 | yes | no | na | +-----+------+-------+-------+----------+---------+

Rick James · Accepted Answer · 2015-06-14 04:59:55Z

SELECT id, name, empid MAX(c_lan) AS c_lan, MAX(Java_lan) AS Java_lan, MAX(Dot_lan) AS Dot_lan FROM tbl GROUP BY id, name, empid;

If you really need no instead of NO, then add LOWER(...).

Edit.

I am assuming a table structure something like

 CREATE TABLE Lans ( // id NAME empid c_lan Java_lan Dot_lan id SMALLINT UNSIGNED NOT NULL, name VARCHAR(50) NOT NULL, empid SMALLINT UNSIGNED NOT NULL, c_lan VARCHAR(10) NULL, java_lan VARCHAR(10) NULL, dot_lan VARCHAR(10) NULL, PRIMARY KEY(id, name) ) ENGINE=InnoDB;

Stack Exchange Network

Merge duplicate rows into one row

1 Answer 1

Hot Network Questions

Merge duplicate rows into one row

1 Answer 1

Related

Hot Network Questions