How to concatenate text from multiple rows into a single text string in SQL server

Question

Consider a database table holding names, with three rows:

Niraj

Dey

Nir

Is there an easy way to turn this into a single string of Niraj,Dey,Nir?

Answer 1

Hello @kartik,

Use COALESCE:

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name 
FROM People

Coalesce is really just a helpful cheat that accomplishes two things:

1) No need to initialize @Names with an empty string value.

2) No need to strip off an extra separator at the end.

The solution above will give incorrect results if a row has a NULL Name value (if there is a NULL, the NULL will make @Names NULL after that row, and the next row will start over as an empty string again. Easily fixed with one of two solutions:

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL

or:

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + 
    ISNULL(Name, 'N/A')
FROM People

Depending on what behavior you want (the first option just filters NULLs out, the second option keeps them in the list with a marker message [replace 'N/A' with whatever is appropriate for you]).

Hope it helps!!

Thank You!!