Concatenate Rows

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.

[cc lang=”sql”]
SELECT
ColumnList = SUBSTRING(
(SELECT
‘,’ + ISNULL(President,”)
FROM dbo.Presidents
FOR XML PATH(”))
,2,8000)
[/cc]

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.
[cc lang=”sql”]
;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
[/cc]

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.

[cc lang=”sql”]
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
[/cc]

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:

[cc lang=”sql”]
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
[/cc]