In SQL Server there are several ways to concatenate columns to a single string. Here are few methods for concatenating columns based on the version of SQL Server.
In SQL Server 2017 and higher
If you have SQL Server 2017 or later, using CONCAT_WS() is the best way to concatenate multiple columns to a string value. Here you have to specify the separator in the form of char, nchar, varchar or nchar as the first argument. Then you can pass on the columns to concatenate in the subsequent arguments. Here is an example.
/** Concatenating Table Columns **/
select CONCAT_WS(' - ', Employee_ID), Employee_Name) As Result from MTB_Table_A
/* Result */
------------------
A002 - William
A003 - Jacob
A004 - Tyler
A005 - Emma
A006 - Ryan
A007 - Ashley
A008 - Brayden
A009 - Olivia
A010 - Michael
A011 - Emily
(10 row(s) affected)
In SQL Server 2012 and higher
CONCAT_WS() is not available in SQL Server 2016 and lower versions. The closest option is to use CONCAT() function along with COALESCE() and manually adding the separators. I will not say that CONCAT() + COALESCE() replaces CONCAT_WS(), but close enough. Here is an example.
SELECT
CONCAT(
COALESCE(CustomerName + ' - ', ''),
COALESCE(PhoneNumber + ' - ', ''),
COALESCE(PostalAddressLine1 + ' - ', ''),
COALESCE(PostalAddressLine2 + ' - ', ''),
COALESCE(PostalPostalCode, '')
) AS Result
FROM Sales.Customers
/* Result */
-------------------------------------------------------------------------
Wingtip Toys (Ruthsburg, MD) - (240) 555-0100 - PO Box 6713 - Shinville - 90451
Eric Torres - (307) 555-0100 - PO Box 4858 - Sandhuville - 90218
Cosmina Vlad - (505) 555-0100 - PO Box 1954 - Gonzalesville - 90602
Bala Dixit - (209) 555-0100 - PO Box 8565 - Blahoville - 90676
Aleksandrs Riekstins - (605) 555-0100 - PO Box 6490 - Linnaville - 90797
Ratan Poddar - (907) 555-0100 - PO Box 6237 - Shakibaville - 90457
Shi Tu - (307) 555-0100 - PO Box 7197 - Nadarville - 90673
Gunnar Lohmus - (201) 555-0100 - PO Box 6430 - Malakarville - 90130
Jackson Kolios - (209) 555-0100 - PO Box 4028 - Lyville - 90693
Alena Kellnerova - (303) 555-0100 - PO Box 2343 - Radniaville - 90143
(10 row(s) affected)
Traditional way
There where no special functions available in SQL Server versions earlier than 2012 for column concatination. So, you have to use the plus (+) string operator along with COALESCE() in the select statement. Here is an example.
SELECT
COALESCE(CustomerName + ' - ', '') +
COALESCE(PhoneNumber + ' - ', '') +
COALESCE(PostalAddressLine1 + ' - ', '') +
COALESCE(PostalAddressLine2 + ' - ', '') +
COALESCE(PostalPostalCode, '') AS Result
FROM Sales.Customers
Reference
- More about CONCAT_WS() function at Microsoft Docs.