SQL Server returning a CSV from a table

Following up my post SQL Server table valued function to return table of integers from a string I also want to do the reverse sometimes – return a csv from a column of values. I use the method demonstrated here:

USE AdventureWorks
GO
-- Check Table Column
SELECT Name
FROM HumanResources.Shift
GO
-- Get CSV values
SELECT SUBSTRING(
(SELECT ',' + s.Name
FROM HumanResources.Shift s
ORDER BY s.Name
FOR XML PATH('')),2,200000) AS CSV
GO

as the comments point out the only issue here is the use of substring with hardcoded params may trip this up. Someone suggested using stuff() which I have not heard of or tested yet.

As I just found out SQL Server 2000 does not support xml path so the alternative to xml is to use a colaesce to build string of csvs from a table column (taken from here)

DECLARE @EmployeeList varchar(100)

SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') +
CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

SELECT @EmployeeList

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s