Programming, SQL Database

Generate CSV (Comma Saparated Values) from table columns

The best way to get CSV data from table by using xml path and it is the best solution for performance and easy way.

For example as below:
Table: Employee
Column: EmployeeType Data

SELECT EmployeeType FROM Employee

EmployeeType
——————–
HR
Clerk
Developers
QA

Option 1:
You can use xml to create CSV as below:

SELECT SUBSTRING(
(SELECT ', ' + e.EmployeeType FROM
Employee e
ORDER BY e.EmployeeType
FOR XML PATH(''))
,2,20000) AS CSV 

Option 2:
You can use COALESCE function as below:

DECLARE @lStr VARCHAR(MAX)
SELECT @lStr = COALESCE(@lStr + ',' , '') + EmployeeType
FROM Employee
SELECT @lStr

Option 3:
You can use string variable to do as below:

DECLARE @listStr VARCHAR(MAX)
SET @lStr = ''
SELECT @lStr = @lStr + EmployeeType + ','
FROM Employee
SELECT SUBSTRING(@lStr , 1, LEN(@lStr)-1

Result:

CSV
——————-
Clerk, Developers, HR, QA

Tagged

Leave a Reply

Your email address will not be published. Required fields are marked *