Concatenate two values from the same column with different conditions in MySQL

For this, you can use group_concat() with aggregate function. Let us first create a table −

mysql> create table DemoTable1869      (      Id int,      Subject varchar(20 ),      Name varchar(20)      ); Query OK, 0 rows affected (0.00 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1869 values(100,'MySQL','John'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1869 values(100,'MongoDB','Smith'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1869 values(101,'MySQL','Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1869 values(101,'MongoDB','Brown'); Query OK, 1 row affected (0.00 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1869;

This will produce the following output −

+------+---------+-------+ | Id   | Subject | Name  | +------+---------+-------+ |  100 | MySQL   |  John | |  100 | MongoDB | Smith | |  101 | MySQL   | Chris | |  101 | MongoDB | Brown | +------+---------+-------+ 4 rows in set (0.00 sec)

Here is the query to concatenate 2 values from the same column with different conditions −

mysql> select Id,concat(StudentFirstName,'',StudentLastName)    from    (       select Id,       max(case when Subject='MySQL' then Name end) as StudentFirstName,       max(case when Subject='MongoDB' then Name end) as StudentLastName       from DemoTable1869       group by Id    )tbl;

This will produce the following output −

+------+---------------------------------------------+ | Id   | concat(StudentFirstName,'',StudentLastName) | +------+---------------------------------------------+ |  100 | JohnSmith                                   | |  101 | ChrisBrown                                  | +------+---------------------------------------------+ 2 rows in set (0.00 sec)