SQL Server COALESCE
Two days I needed to take a block of records and turn the rows into a horizontal comma separated list. That comma separated list in turn needed to be the value for a column in a larger query.
I googled and found quite a few potential solutions and finally settled on using the COALESCE function. You certainly could use temp tables or cursors accomplish this. But the COALESCE function, with help from Arian and a bit of my tweaking ended up being a very succinct and easy to read solution.
DECLARE @list NVARCHAR(MAX)
SELECT @list = COALESCE(@list+’, ‘,”)+[FirstName] +’ ‘+[LastName]
Of course there were some joins I needed to do in the FROM clause to get the correct data and it was also parameterized, but this was the gist of it.
I pushed the above snippet into a function, changed the last SELECT statement to a RETURN statement(for the sake of the function) and then used that function in the larger query to show the list of people as a comma separated list. It worked just as intended and solved the issue at hand.