How to concatenate row data into string using SQL

Very simple way to achieve this is to use FOR XML statement. The result of the following example is list of user names including first and last name separated by commas.

SELECT FirstName+' '+LastName+', ' FROM UserProfile FOR XML PATH('')

But the result string ends with a comma and space. We can either remove it by SUBSTR or REPLACE functions. Using REPLACE as it is shown below saves the usage of subquery or calcualtion of the length of the same expression.

SELECT
REPLACE((REPLACE((REPLACE(
(SELECT FirstName+' '+LastName FROM UserProfile FOR XML PATH('A'))
,'</A><A>',', '))
, '</A>',''))
,'<A>','')

Here is another way to achieve this which might be convinient in stored procedures.

DECLARE @Names VARCHAR(8000)
set @Names=''
SELECT @Names = @Names + CASE WHEN @Names<>''
THEN ', ' ELSE '' END + Name
FROM Users
SELECT @Names