Creative Notice

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.

Share/Bookmark