For some reason this is one of my most visited posts…
We may have several records belonging to a column or field that we want to concatenate and display in a single string. Something like this:
description =========== value1 value2 value3
and we want to get something like this:
value1, value2, value3
that is, as if we had to transpose the column, but without complicating ourselves.
The solution is:
DECLARE @values VARCHAR(1000)
SELECT @values= COALESCE(@values + ', ', '') + description FROM folders
select @values as values
The magic is done by the COALESCE command that returns the expression that does not contain the NULL value among its arguments.
NOTE: This solution is valid only if no row is NULL.