.gif)
This topic is about sql server Comma Separated Values(csv) from Table Column.
There are Many times where we need to convert a set of rows in a single column to a comma separated (CSV) string , in this article i am going to tell you how you can get csv values from sql server table column.
For this sql server query example i have created a Temporary Table in Sql Server Database.
CREATE TABLE #MyTable(Column1 NVARCHAR(10))
INSERT INTO #MyTable VALUES('sdf')INSERT INTO #MyTable VALUES('dfg')INSERT INTO #MyTable VALUES('ghj')INSERT INTO #MyTable VALUES('rty')INSERT INTO #MyTable VALUES('hjk')
Here i am showing Two methods to create Comma Separated Values(csv) from Table Column.
1-Create Comma Seperated Values(CSV) Using COALESCE Method
DECLARE @MakeStr VARCHAR(200)
SELECT @MakeStr = COALESCE(@MakeStr+',' ,'') + Column1
FROM #MyTable
SELECT @MakeStr as MyCSV
2-Create Comma Seperated String Using XML Method
DECLARE @MakeStr VARCHAR(200)
SELECT SUBSTRING(
(SELECT ',' + Column1
FROM #MyTable
ORDER BY Column1
FOR XML PATH('')),2,200) AS MyCSV
And the result for the above Sql Query Will be
MyCSV
——————–
sdf,dfg,ghj,rty,hjk
MyCSV
——————–
dfg,ghj,hjk,rty,sdf
And the result for the above Sql Query Will be
MyCSV
——————–
sdf,dfg,ghj,rty,hjk
MyCSV
——————–
dfg,ghj,hjk,rty,sdf
