How to Merge Duplicate Rows into One Row in MySQL

duplicationMySQL

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      |
+-----+------+-------+-------+----------+---------+

Best Answer

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;
Related Question