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

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)
Updated on: 2019-12-26T07:35:39+05:30

1K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements