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 […]