Combine Two Columns in SQL
In SQL, columns are fundamental block of any relational database. Without columns and rows, a relational database would be unable to store the structured data using records and attributes.
One of the most fundamental and common tasks in relational databases is combining two or more columns into a single entity. Column combination allows us to create new columns by merging the data from two or more existing columns within the database.
In this tutorial, we will explore how we can combine two columns in SQL. For demonstration purposes, we will use the Sakila sample database which provides an extensive dataset for a wide variety of use cases.
Prerequisites:
Before we start, make sure that you have MySQL installed and you have an access to the Sakila sample database.
If you do not have the Sakila sample database installed or you wish to use another dataset, feel free to do so.
Method 1: Column Concatenation
One of the best methods that we can use to combine two database columns is using the concat() function. This function allows us to concatenate two or more columns into a single unit.
Take the following query for example:
FROM actor;
In this example, we use the concat() function to concatenate the value of the “first_name” and “last_name” columns from the “actor” table. We then create a new column called “full_name” from the resulting set.
An example output is as follows:
--------------------+
PENELOPE GUINESS |
NICK WAHLBERG |
ED CHASE |
JENNIFER DAVIS |
JOHNNY LOLLOBRIGIDA |
BETTE NICHOLSON |
Method 2: Using the CONCAT_WS Function
We also have access to the CONCAT_WS function which behaves closely similar to the CONCAT() function. However, unlike the CONCAT() function, the CONCAT_WS() function allows us to define a separator between the concatenated values.
This can be useful if you need a custom formatting without the need for external parsing. For example, we want to the full name to follow the “first_name.last_name” format as shown in the following example query:
FROM actor;
Similarly, we combine the “first_name” and “last_name” columns into a single column. However, we separate the values using a dot character.
The resulting output is as follows:
--------------------+
PENELOPE.GUINESS |
NICK.WAHLBERG |
ED.CHASE |
JENNIFER.DAVIS |
JOHNNY.LOLLOBRIGIDA |
Method 3: Concat with Conditionals
We can also combine two conditional columns by adding a basic CASE statement. Let us take an example demonstration:
CASE
WHEN rental_rate > 2.0 THEN CONCAT(title, ' (Expensive)')
ELSE CONCAT(title, ' (Affordable)')
END AS movie_pricing
FROM film;
For our case, we combine the “title” column with a condition based on the “rental rate” column. This creates a column called “movie_pricing” which adds a metadata to the title on whether the rental rate is expensive or affordable.
An example output is as follows:
---------------------------------------+
ACADEMY DINOSAUR (Affordable) |
ACE GOLDFINGER (Expensive) |
ADAPTATION HOLES (Expensive) |
AFFAIR PREJUDICE (Expensive) |
AFRICAN EGG (Expensive) |
Method 4: Concat with Aliases and Join
A more readable approach is giving the columns with aliases and then joining the aliases values into a single unit.
An example is as follows:
CONCAT(customer.first_name, ' ', customer.last_name) AS customer_name,
CONCAT(staff.first_name, ' ', staff.last_name) AS staff_name
FROM customer
JOIN staff ON customer.support_rep_id = staff.staff_id;
Example Output:
---------------------+------------+
MARY SMITH |Mike Hillyer|
PATRICIA JOHNSON |Mike Hillyer|
LINDA WILLIAMS |Mike Hillyer|
Conclusion
In this tutorial, we learned all about the methods and techniques of combining two columns in an SQL database to create a new database.
Source: linuxhint.com