There have been a few times where I’ve needed to return the values of multiple columns as one column containing a CSV list of those values.
This is accomplished using a sub-query like so…
USE AdventureWorks GO -- Check Table Column SELECT Name FROM HumanResources.Shift GO -- Get CSV values SELECT STUFF( (SELECT ',' + s.Name FROM HumanResources.Shift s ORDER BY s.Name FOR XML PATH('')),1,1,'') AS CSV GO
The above example came from Pinal Dave’s great post on SQL Authority. I substituted his use of SUBSTRING for STUFF since it will handle any length content.
csv, stuff, xml path