Combine Columns in SQL Server

In this article, we can see the various sql queries to combine two columns.



Concatenation Using + Operator

Syntax:

SELECT (ColumnA + ColumnB) AS ColumnZ
FROM Table;

Example


SELECT [First Name], [Last Name], [First Name] + ' ' + [Last Name] AS 'Full Name' FROM Employee

In this example we have combined First and Last Name of a person to get the Full Name.


Result


First Name Last Name Full Name

Jack Benny Jack Benny
Michael Hussain Michael Hussain
Roberts Tennison Roberts Tennison



Concatenation Using Union Operator

The sql keyword Union is used to combine the two columns.

Syntax:

SELECT ColumnA AS ColumnZ
FROM Table

UNION --Union Keyword to combine two columns

SELECT ColumnB AS ColumnZ
FROM Table
ORDER BY ColumnZ


Example

SELECT user_login AS user_idee, employee FROM employee, grpmebrs
WHERE USER_ID = user_login
UNION
SELECT secgroup AS user_idee, employee FROM employee, grpmebrs
WHERE USER_ID = user_login


This will also list the same thing.

Effectivly the result is the same.

Combine Null Columns

The Concatenation using "+" Fails when either column is Null.

Solution:

The isNull Function is used to check whether the column is Null.


Example With isNull Function


Select isnull(CAST(Customer.Contactid as nvarchar(10)),'') +'-'+ isnull(Customer.Firstname,'') +'-'+ isnull(Customer.LastName,'') as [Full Name] from Customer where customerid = 1823


Result

1823-Michael-Dizousa