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)

Arjun Thakur

Published on 01-Apr-2019 09:24:45

• Related Questions & Answers
• Update two columns with a single MySQL query
• Combine columns before matching it with LIKE in a single query in MySQL?
• Count two different columns in a single query in MySQL?
• How to combine different columns of a table to yield a single column in query output in PostgreSQL?
• Comparing two columns in a single MySQL query to get one row?
• Order by a function of two columns in a single MySQL query
• How to alter column type of multiple columns in a single MySQL query?
• MySQL query to sort multiple columns together in a single query
• Change multiple columns in a single MySQL query?
• How can we combine values of two or more columns of MySQL table and get that value in a single column?
• A single MySQL query to combine strings from many rows into a single row and display the corresponding User Id sum in another column?
• MySQL query to display columns name first name, last name as full name in a single column?
• How to set all values in a single column MySQL Query?
• How to add column and index in a single MySQL query?
• Select distinct names from two columns in MySQL and display the result in a single column