MySQL query to combine two columns in a single column?
You can use COALESCE() function for this. In the COALESCE() function, it returns the first NON NULL value from the column. To understand the concept, let us first create a demo table
mysql> create table combineTwoColumnsDemo
-> (
-> UserId int,
-> UserName varchar(20),
-> UserAge int
-> );
Query OK, 0 rows affected (1.12 sec)
Insert some records in the table using insert command. The query is as follows −
mysql> insert into combineTwoColumnsDemo values(101,'John',23);
Query OK, 1 row affected (0.16 sec)
mysql> insert into combineTwoColumnsDemo values(102,'Carol',20);
Query OK, 1 row affected (0.14 sec)
mysql> insert into combineTwoColumnsDemo values(103,'Bob',25);
Query OK, 1 row affected (0.13 sec)
mysql> insert into combineTwoColumnsDemo values(104,'Mike',26);
Query OK, 1 row affected (0.18 sec)
mysql> insert into combineTwoColumnsDemo values(105,NULL,23);
Query OK, 1 row affected (0.22 sec)
mysql> insert into combineTwoColumnsDemo values(105,'Maxwell',NULL);
Query OK, 1 row affected (0.15 sec)
Now you can display all records from the table using select statement. The query is as follows −
mysql> select *from combineTwoColumnsDemo;
The following is the output
+--------+----------+---------+
| UserId | UserName | UserAge |
+--------+----------+---------+
| 101 | John | 23 |
| 102 | Carol | 20 |
| 103 | Bob | 25 |
| 104 | Mike | 26 |
| 105 | NULL | 23 |
| 105 | Maxwell | NULL |
+--------+----------+---------+
6 rows in set (0.00 sec)
Here is the query to combine two columns in a single column
mysql> SELECT UserName,
-> UserAge,
-> COALESCE(UserName, UserAge) AS Combine_UserName_UserAge
-> FROM combineTwoColumnsDemo;
The following is the output
+----------+---------+--------------------------+
| UserName | UserAge | Combine_UserName_UserAge |
+----------+---------+--------------------------+
| John | 23 | John |
| Carol | 20 | Carol |
| Bob | 25 | Bob |
| Mike | 26 | Mike |
| NULL | 23 | 23 |
| Maxwell | NULL | Maxwell |
+----------+---------+--------------------------+
6 rows in set (0.00 sec)
Published on 01-Apr-2019 12:54:45
