This solution does not require a variable and it also allows you to do joins against other tables within one statement. The substring function removes the leading comma. Download the presidents table here.

SELECT
	ColumnList = SUBSTRING(
				(SELECT
					',' + ISNULL(President,'')
				FROM dbo.Presidents
				FOR XML PATH(''))
			,2,8000)

Using a common table expression (CTE) you can also recursively union the CTE with itself in order to generate a comma delimited list. This is a useful function for a CTE. The CTE itself does concatenate more than what is needed however it’s still relatively clean. Download the presidents table here.

;WITH PresidentList (PresidentNumber, President) AS
(
    SELECT
    1
    , CAST('' AS VARCHAR(max))
    UNION ALL
    SELECT
		cte.PresidentNumber + 1
		,cte.President +  pl.President + ', '
    FROM (
		SELECT
		RowNum = Row_Number() OVER (ORDER BY PresidentNumber)
		,President
		FROM dbo.Presidents
	) pl
    JOIN PresidentList cte ON pl.RowNum = cte.PresidentNumber
)

SELECT TOP 1 President FROM PresidentList ORDER BY PresidentNumber DESC

The next two examples require a variable, and when using a variable you cannot display any other columns from the table in the result set.

There is a misnomer floating around much of the TSQL community with regards to concatenating strings using COALESCE. In actuality, this has nothing to do with COALESCE. COALESCE is simply used to replace a potential NULL value in one of the rows. Here is the code without using a COALESCE.

DECLARE @ColumnList varchar(8000)
SET @ColumnList = '' -- Set this to empty string

SELECT @ColumnList = @ColumnList + ',' + ColumnName
FROM Table1

-- Remove the leading comma
SET @ColumnList = SUBSTRING(@ColumnList,2,8000)

select @ColumnList

This works fine, the only problem is that if the columnName field contains a NULL, then the column list variable will be set to NULL because concatenating a NULL to anything will always return a NULL.

The other problem I have with using COALESCE is that ISNULL is proven to be faster. So to rewrite the above statment to account for NULLs, I would use ISNULL like such:

DECLARE @ColumnList varchar(8000)
SET @ColumnList = '' -- Set this to empty string

SELECT @ColumnList = @ColumnList + ',' + ISNULL(ColumnName,'')
FROM Table1

-- Remove the leading comma
SET @ColumnList = SUBSTRING(@ColumnList,2,8000)

select @ColumnList

• sql server concatenate rows
• sql concatenate rows
• sql server 2008 concatenate rows
• concatenate rows in sql server
• concatenate rows in sql server 2008