SQL Tip – Comma delimited list from a table column

If you want to extract query result of a column to a variable as Comma separated values list use the following example and modify accordingly.


declare @Dblist varchar (max)
set @Dblist = ''

select @Dblist =
case when @Dblist = ''
then Name
else @Dblist + coalesce(',' + Name, '')
end
from sys.databases dbs
where dbs.is_read_only = 0


In above example tip we collect the names of databases on the server which are not in read-only mode as a comma values string so it could be fed to another Stored procedure to do Index maintenance.