MS SQL Server - concatenate multiple columns into one

In this article, we would like to show you how to concatenate multiple columns into one in MS SQL Server.

Quick solution:

SELECT CONCAT([column1], 'separator', [column2], 'separator', [columnN]) AS 'alias_name'
FROM [table_name];

SELECT CONCAT_WS('separator', [column1], [column2], [columnN]) AS 'alias_name'
FROM [table_name];

Practical example

To show how to combine multiple columns into one, we will use the following table:

Note:

At the end of this article you can find database preparation SQL queries.

Example 1 - using CONCAT()

In this example, we will concatenate name and surname columns into one - full_name using empty string (' ') as a separator. Then we will combine the rest of the columns into the second one - address using commas (', ') as separators.

Note:

Go to the next example to see how to specify the separator (such as ', ') only once when you want to concatenate many columns.

Query:

SELECT 
    CONCAT([name], ' ', [surname]) AS 'full_name',
    CONCAT([country], ', ', [city], ', ', [street]) AS 'address'
FROM [users];

Output:

Example 2 - using CONCAT_WS()

In this example, we will concatenate country, city and street columns into one - address, using CONCAT_WS() function.

Query:

SELECT
    [name], [surname], 
    CONCAT_WS(', ', [country], [city], [street]) AS 'address'
FROM [users];

Output:

Database preparation

create_tables.sql file:

CREATE TABLE [users] (
	[id] INT IDENTITY(1,1),
	[name] VARCHAR(50) NOT NULL,
	[surname] VARCHAR(50) NOT NULL,
	[country] VARCHAR(100) NOT NULL,
	[city] VARCHAR(100) NOT NULL,
	[street] VARCHAR(100) NOT NULL,
	PRIMARY KEY ([id])
);

insert_data.sql file:

INSERT INTO [users]
	( [name], [surname], [country], [city], [street])
VALUES
	('Tomasz', 'Kowalski', 'Poland', 'Warsaw', 'Mickiewicza Street 1'),
	('Chris', 'Brown', 'United States', 'New York', 'Main Street 12'),
	('Minh', 'Nguyen', 'Vietnam', 'Hanoi', 'Dong Xuan Street 123');