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)
How would you address column data as ‘Fruit=Apple,Color=Red,Source=Tree’. I want a columns for Fruit, Color, Source with their respective values, Apple, Red, Tree returned.
VERY NICE BUT…
try this on a numeric or integer field. int won’t convert to varchar.
Just cast it works pretty well, CAST(myId as varchar). Nice article BTW.
Output comes as DayNight like that and not returns only comma separated string values. Kindly assist.
What’s the command you are running?
What happens if the source column is text already containing a quote .. does it escape the quote?