Getting a string array from SQL Server

After the last post showing how to pass an array into SQL, i will show how to go the other way around. How to get a string array from a table.

I should note that i ONLY pass and get arrays as strings on very specific cases (usually perf, test or dbg related).
Whenever possible i use relational operations only, especially for returning collections. Sometimes it is useful though to be able to return a single column with more than one elm, especially when the actual destination of the data is a function that expects a delimiter separated list.

That said, getting a bunch of params as a string array can be done like this:

DECLARE @outStringArray VARCHAR(MAX)
SET @outStringArray = NULL

SELECT @outStringArray = ( COALESCE( NULLIF( @outStringArray + ',', '' ), '' ) + Name )
FROM sys.objects

SELECT @outStringArray

The interesting part is COALESCE( NULLIF( @outStringArray + ',', '' ), '' ) + Name which should read:
if @outStringArray is not NULL, then return the current string + ',' + Name. If it is null, return '' + Name.

We return '' + Name, because if we return NULL + Name, then NULL + something is NULL and our string would never grow.
You could also use SELECT CASE WHEN syntax, but i prefer this more compact form of saying the same. (after some time using this on queries you learn the meaning quite fast)
You could also use SQLCLR, xml (though not exactly a comma separated thing) and recursive functions.

Again, you can have specialized versions to deal with NULLs in the string (doing + COALESCE( Name, 'N/A') instead of + Name for example), to deal with different types, as well as different sort orders, more than one string field (e.g: + Name + ':' + Value).

Another use for this is sometimes when you want to compare hierarquical data the queries can become complex, and this way you can kind of serialize the hierarquies and then do a simple string comparison... (more on this on future posts)