SQL SERVER – Comma Separated Values (CSV) from Table Column

I use following script very often and I realized that I have never shared this script on this blog before. Creating Comma Separated Values (CSV) from Table Column is a very common task, and we all do this many times a day. Let us see the example that I use frequently and its output.

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

I consider XML as the best solution in terms of code and performance. Further, as I totally prefer this option, I am not even including the linka to my other articles, where I have described other options.

Do you use any other method to resolve this issue? Can you find any significant difference in performance between these options? Please leave your comment here.

UPDATE: SQL Server 2016 has new feature of JSON which now removes the need of using XML for the purpose of CSV. Here are few of the related articles for SQL Server 2016 and JSON.

• SQL SERVER – 2016 – Opening JSON with OPENJSON()
• SQL SERVER – 2016 – Check Value as JSON With ISJSON()
• SQL SERVER – JSON Support for Transmitting Data for SQL Server 2016 – Notes from the Field #114
• SQL SERVER – Getting started and creating JSON using SQL Server 2016

Reference: Pinal Dave (https://blog.sqlauthority.com)