1

I have row 1 and 2 which they are similar in records except status column.

   +-----+------+-------+-----------+----------+---------+
| #id | NAME | STATUS|   c_lan   | Java_lan | Dot_lan |
+-----+------+-------+-----------+----------+---------+
|   1 |  A   |   111 |      yes  |    Q     | W       |
|   1 |  A   |   222 |      yes  |    Q     | W       |
+-----+------+-------+-----------+----------+---------+

Now is it possible to have these tow rows (1,2) become as one row as like below (need to merge status values in to one cell)?

+-----+------+-----------+-----------+----------+---------+
| #id | NAME | STATUS    |   c_lan   | Java_lan | Dot_lan |
+-----+------+-----------+-----------+----------+---------+
|   1 | A    |   111,222 |      yes  |    Q     | W       |
+-----+------+-----------+-----------+----------+---------+

noting that i am considering this to be done on mysql view

1

Use GROUP_CONCAT() for this.

Your table:

create table foo
(
id int,
name varchar(10),
status int,
c_lan varchar(10),
Java_lan varchar(10),
Dot_lan varchar(10)
);

insert into foo values (  1,'A',111,'yes','Q','W'   );

insert into foo values (  1,'A',222,'yes','Q','W'   );

Testcase:

mysql> select id, GROUP_CONCAT(status separator ',') as status, c_lan, Java_lan, Dot_lan from foo group by id;
+------+---------+-------+----------+---------+
| id   | status  | c_lan | Java_lan | Dot_lan |
+------+---------+-------+----------+---------+
|    1 | 111,222 | yes   | Q        | W       |
+------+---------+-------+----------+---------+
1 row in set (0.00 sec)

mysql>

View:

create view concatview as (
select id, GROUP_CONCAT(status separator ',') as status, c_lan, Java_lan, Dot_lan
from foo
group by id
);

Test:

mysql> select * from concatview;
+------+---------+-------+----------+---------+
| id   | status  | c_lan | Java_lan | Dot_lan |
+------+---------+-------+----------+---------+
|    1 | 111,222 | yes   | Q        | W       |
+------+---------+-------+----------+---------+
1 row in set (0.00 sec)

mysql>

Obviously you may want to GROUP on more than just id (depending on what you consider a "duplicate", and may want to order the GROUP_CONCAT() in some way, but this points you in the right direction.

Edit to address your comment:

mysql> insert into foo values ( 2,'b',555,'yes','Q','W' );
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select id, GROUP_CONCAT(status separator ',') as status, c_lan, Java_lan, Dot_lan
    -> from foo
    -> group by id
    -> ;
+------+---------+-------+----------+---------+
| id   | status  | c_lan | Java_lan | Dot_lan |
+------+---------+-------+----------+---------+
|    1 | 111,222 | yes   | Q        | W       |
|    2 | 555     | yes   | Q        | W       |
+------+---------+-------+----------+---------+
2 rows in set (0.01 sec)

mysql>
| improve this answer | |
  • well that caused to return one row that has in its status coloumn a group of 111,222 and all other status values unrelated to id 1 ,i have another id's i didnt mentioned in the example 3,4,5,6,7,... – Suhayb Oct 5 '16 at 12:17
  • try to insert into foo values ( 2,'b',555,'yes','Q','W' ); and you will have :just one row returned 1 | 111,222,333 | yes | Q | W – Suhayb Oct 5 '16 at 12:20
  • Not true. Question edited. – Philᵀᴹ Oct 5 '16 at 12:23
  • Whereas this answer show how to do it, you should think twice about ever doing it. Searching a commalist is a pain, can't use an index, etc. – Rick James Oct 5 '16 at 17:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.