- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
Updated on 30-Jul-2019 22:30:25
- 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?
- MySQL SELECT from two tables with a single query
Advertisements