SQL Columns to CSV List

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.

Coldfusion Method to Convert a Query to CSV

I’ve been working with Magento recently and finally came to the point where I needed to import data from MSSQL into Magento’s MySQL. First idea in the pipe was to use build an exporter in our existing CF arcetecture and import the resulting output using Magento’s DataFlow profiles.

I’ve since decided to fore go that setup and go for the gold with Magento’s API (that’s a story for another time!). But while still working on that short lived DataFlow import idea found my self needing to convert a CF query object into CSV. Some Googling lead me to Ben Nadal’s place and some example code.

I’ve since modified his code to fit my purposes and present it here for your purusement. The main difference between Ben’s snippet and my own is the use of script syntax and I’m just looping over query.columnlist for the header row rather than passing in a list of column names. I didn’t need to limit columns returned in the CSV