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]

3 comments
Alex 24 Feb 2012 at 4:54 am

Hi Derek,

I was looking around for a clean answer to this problem and yours is the best by far.
Lots of other approaches proposed by other people but they are all too complicated.

Thanks
Alex

Derek 06 Aug 2010 at 9:42 am

Joey,

You can do it by joining against the table in the inline select. Like this:

SELECT
Table_Name,
ColumnList = SUBSTRING(
(SELECT
‘,’ + ISNULL(Column_Name,”)
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE isc.TABLE_NAME = c.TABLE_NAME
FOR XML PATH(”))
,2,8000)
FROM INFORMATION_SCHEMA.COLUMNS isc
GROUP BY TABLE_NAME

Good Luck, Derek

Joey 06 Aug 2010 at 12:43 am

Hi,

Nice function, if you want to group this by a specified record, how do you handle this?
for example get all the order item id’s on a row with one Order ID

Regards,
Joey

Featured Articles

 Site Author

  • Thanks for visiting!