Retrieve Comma Separated Values

Some times there are certain scenarios where data needs to be gathered as a collection. For instance multiple records need to be set as single record, in a such cases setting row data as comma separated column value can be quite effective.

SQL server allows to incorporate this with its built-in feature of stuff allowing row data to be merged as a single column value.

Code:

select  

(STUFF((SELECT ',' + CAST(a.username AS VARCHAR(10))+''' ' [text()]

FROM [users] a

FOR XML PATH(''), TYPE)

.value('.','NVARCHAR(MAX)'),1,2,' '))

as AllNames 

Output: