Concatenate columns in SQL query result from a single table

Question

I have query that looks something like the following:

select firstname, lastname
from names
where firstname = 'john'

I would like to have something like the following (conceptually):

select
[names.firstname + names.lastname] as 'fullname'
from names
where names.firstname = 'john'

Of course, this returns an invalid column name 'names.firstname + names.lastname'.

Is it possible to return a single aliased column containing the result of two columns from the same table?

select [firstname] +', ' +[lastname] as 'fullname' from names where names.firstname = 'john' — Ben
– Ben, Commented Jan 22, 2016 at 20:48
What version of SQL Server? What is the desired result if either column is null? — Martin Smith
– Martin Smith, Commented Jan 22, 2016 at 20:50
The concat function might be an option if your server version is 2012+ — jpw
– jpw, Commented Jan 22, 2016 at 20:51
@MartinSmith, SQL Server 2012; If either column is null, I suppose a blank would be fine. Although I might consider catching null cases with something like ISNULL(firstname, '(No First Name) ') ? — Wes Doyle
– Wes Doyle, Commented Jan 22, 2016 at 20:52
The reason you got that error was because of the brackets you put around the expression. And don't use quotes around the alias. — shawnt00
– shawnt00, Commented Jan 22, 2016 at 21:43

Wes Doyle · Accepted Answer · 2016-01-22 20:56:25Z

1

Thanks for the comments; In this case it looks like the following will work as needed:

    CONCAT(ISNULL(names.FirstName, '(No First Name)', 
           ' ', ISNULL(names.LastName, '(No Last Name)')) AS 'FullName'

answered Jan 22, 2016 at 20:56

Wes Doyle

2,2973 gold badges19 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Remove the quotes from the alias.