How to write a query to Concatenate Rows in SQL Server to form a String with an example?. It is one of the common Interview Questions that you might face in interviews. For this concat rows example, We use the below-shown data.

SQL Concatenate Rows into String Example
In this example, we will show you how to Concatenate Rows using the COALESCE Function.
DECLARE @LongStringFirstNames VARCHAR(MAX) SELECT @LongStringFirstNames = COALESCE(@LongStringFirstNames + ', ', '') + [FirstName] FROM [Customers] SELECT @LongStringFirstNames AS [List of Name]

Let me combine the Last Name rows as well
DECLARE @LongStringFirstNames VARCHAR(MAX) DECLARE @LongStringLastNames VARCHAR(MAX) SELECT @LongStringFirstNames = COALESCE(@LongStringFirstNames + ', ', '') + [FirstName] FROM [Customers] SELECT @LongStringLastNames = COALESCE(@LongStringLastNames + ', ', '') + [LastName] FROM [Customers] SELECT @LongStringFirstNames AS [List of First Name], @LongStringLastNames AS [List of Last Name]

How to Concatenate Rows Example 2
It will show you, How to Concatenate Rows into strings using the CONCAT Function.
DECLARE @LongStringFirstNames VARCHAR(MAX) SELECT @LongStringFirstNames = CONCAT(@LongStringFirstNames + ', ', '') + [FirstName] FROM [Customers] SELECT @LongStringFirstNames AS [List of First Name]

Let me concatenate the Last Name rows as well
DECLARE @LongStringFirstNames VARCHAR(MAX) DECLARE @LongStringLastNames VARCHAR(MAX) SELECT @LongStringFirstNames = CONCAT(@LongStringFirstNames + ', ', '') + [FirstName] FROM [Customers] SELECT @LongStringLastNames = CONCAT(@LongStringLastNames + ', ', '') + [LastName] FROM [Customers] SELECT @LongStringFirstNames AS [List of First Name], @LongStringLastNames AS [List of Last Name]

Concatenate Rows into String Example 3
In this example, we will show you, How to Concat Rows in SQL Server using the SPACE and XML PATH.
SELECT STUFF(( SELECT ',' + SPACE(1) + [FirstName] FROM [Customers] FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS [List Of First Names]

Let me combine the rows from two columns (First Name and Last Name) using the SQL stuff function and For XML path.
SELECT STUFF(( SELECT ',' + SPACE(1) + [FirstName], ',' + SPACE(1) + [LastName] FROM [Customers] FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS [List Of Names]
