G. Langlois
G. Langlois

Reputation: 75

How to join columns from the same table in a SQL query to show them as one?

I just have a quick question about how to do something, but also about whether or not this is even possible in the first place. I've tried to find an answer online, but got no success.

I'll create a very simplified scenario that's not necessarily what I want to do precisely, but it's a pretty good example. Let's say that I have this table

Users
----------------------------------------------
| ID | FirstName | LastName | Username | ... |
|----|-----------|----------|----------|-----|
|   1| John      | Doe      | jdoe     | ... |
|   2| James     | Smith    | jsmith   | ... |
|   3| Jane      | Fisher   | jfisher  | ... |
|   x| ...       | ...      | ...      | ... |
----------------------------------------------

Let's say that I want to create a query to show only the FirstName and LastName, but as a full name.

I could do:

SELECT Users.FirstName, Users.LastName
FROM Users
WHERE ID > 2

What I want however is something that would give me FirstName and LastName as one column that I'd present like this:

(Users.FirstName, Users.LastName) AS 'Full Name'

Therefore, I'd get something like:

Users
---------------------------------------
| ID | Full Name     | Username | ... |
|----|---------------|----------|-----|
|   1| John Doe      | jdoe     | ... |
|   2| James Smith   | jsmith   | ... |
|   3| Jane Fisher   | jfisher  | ... |
|   x| ...           | ...      | ... |
---------------------------------------

Of course, in my real query, I'd be joining a bit more than just 2 columns, but I think you get the point. So is this possible? If so, how can I do it.

Upvotes: 1

Views: 50

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

You are using MySQL. While the standard string concatenation operator is ||, it doesn't work with MySQL. Use the function CONCAT_WS:

select concat_ws(' ', firstname, lastname)
from mytable;

(You could also use the function CONCAT, but I prefer CONCAT_WS in this situation.)

Here is the docs: https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_concat-ws

Upvotes: 1

Randy
Randy

Reputation: 16677

SELECT first_name + ' ' + last_name as full_name from users

in Oracle

SELECT first_name || ' ' || last_name full_name from users

edit: maybe if you are having data type issues - you could force it to think it's a string - something like

SELECT '' + first_name + ' ' + last_name from users

Upvotes: 1

Related Questions