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)
126 Comments. Leave new
i am getting an error like Incorrect syntax near ‘XML’.wen i used tat
SELECT id,
SUBSTRING(
(
select ‘,’ + CAST(value AS VARCHAR)
from table1 b
WHERE a.id = b.id
FOR XML PATH(”)
)
,3,100)
FROM table1 a
GROUP BY a.ID
Hello Tejas,
i am getting an error like Incorrect syntax near ‘XML’.wen i used tat
SELECT id,
SUBSTRING(
(
select ‘,’ + CAST(value AS VARCHAR)
from table1 b
WHERE a.id = b.id
FOR XML PATH(“)
)
,3,100)
FROM table1 a
GROUP BY a.ID
Hi Naveen,
What is SQL SERVER you are using?
It should work on 2005 and above only.
XML DataType feature is available from SQL 2005 and above.
Please let us know. I can run this query at my end on SQL 2005 and SQL 2008.
Thanks,
Tejas
if i don’t have Value for Any Column then its Output Comes As ,,Night as in Your Example Can you Explain How to Apply Case Statement here in Stuff.So that , will not be Come if Value Is not Present for that Column
To avoid length problems, I have done
RIGHT((SELECT ',' + [LineString] FROM @firstSort ORDER BY [FirstPoint] DESC FOR XML PATH('')),LEN((SELECT ',' + [LineString] FROM @firstSort ORDER BY [FirstPoint] DESC FOR XML PATH('')))-1)I just grab the right side of the string minus 1. That way you don’t accidentally cut anything off.
HI,
I have a table named ‘Category_Master’ and it has a column named ‘category’ where the values are stored as follows
category
———–
a,s,d
z,x,c
q,w,e
when i execute the following query:
select LEFT(category, CHARINDEX( ‘,’, Artist_Cat) – 1) from Category_Master
i found output as ….
category
———–
a
z
q
i.e, I’m getting only first comma separated values as result, but I want the remaining comma separated values in separate table as the result.
i.e I need the output as :
category
———–
a
z
q
category
———–
s
x
w
and
category
———–
d
c
e
Please send me the proper query in order to get the above output as result.
Thank you in advance
regards,
zakir.
Search about Split function+SQL Server in google/bing
Thanks alot……..
I got an split function.
Suggest some better work for mysql
Regards
Zakir
hi
i have a problem to splite 2 words which is present in one column the first word is in english & the second word is in arabic meaning first & second word are same but in different language so now i want to seperate english word so that i can proceed my work as sql is not understanding arabic character in where condition
example
status code
submitted ارسلت
valid صحيح
unsent غيرمرسل
select left(column,charindex(‘ ‘,column)-1) from your_table
Hi Dave,
i am new to this field,recently i gone through one problem..
that i want to share with u and all readers and require assist for that.
i have created table with check constraint :
create table check1
(id int,
salary int check(salary >=1000 and salary<=5000));
inserted value through csv file:
1,500
2,1000
then i have done bulk insert:
bulk insert check1
from 'd:\csv.txt'
with
(fieldterminator=',',
rowterminator='\n')
and out put :
id | salary
1 500
2 1000
this is wrong output i m geting near id=1 where salary is 500 which is not acording to check constraint
plz Dave late me know were i m gone wrong
thanks in advance
Use this
bulk insert check1
from ‘d:csv.txt’
with
(fieldterminator=’,’,
rowterminator=’n’,
check_constraints)
thanks madhivanan……
it’s working…….
madhivanan, i have one more doubt with above example,
if table having primary key or other constraints i have to go with same method…
Yes. You have to use the same method
i m stuck in one problem..which are the new record inserted into the table, i want to know which are the record are inserted..
emp{e_id,ename,e_dept}
thanks in advance
It is possible if you have a datetime column with defulat value of getdate()
you are amazing sir .
you always save my day ..
BTW : i used to do this using a UDF but the performance is terrible
Declare @result varchar(1000)
Set @result = ”
Select @result = @result + ColName+ ‘,’ From myTbl
Set @Result = Left(@result , Len(@Result) -1)
thanks a million
Sir,
I need to swap 3 rows to 1 columns..
Input Format :
ID Columns
————————–
1000 Data – 1
1000 Data – 2
1000 Data – 3
Output Should be…
ID Column1 Column2 Column3
——————————————————
1000 Data – 1 Data – 2 Data – 3
i have 3 table NNCE,MAM.ROVE
NNCE
——-
ID NAME DEPARTMENT
—————————-
1 MANI ECE
2 RAJA ECE
3 UDHAYANAN ECE
4 KARTHIK ECE
MAM
ID NAME DEPARTMENT
————————–
1 MANI IT
2 RAJA IT
3 UDHAYANAN IT
4 KARTHIL IT
ROVER
——–
ID NAME DEPARTMENT
——————————
1 MANI MCA
2 RAJA MCA
3 UDHAYANAN MCA
4 KARTHIK MCA
NOW I WANT OUTPUT
ID NAME DEPARTMENT
———————————-
1 MANI ECE,IT,MCA
2 RAJA ECE,IT,MCA
3 UDHAYANAN ECE,IT,MCA
4 KARTHIK ECE,IT,MCA
PLEASE SENT QERRY
Wow. I’ve been doing this a much harder way. Your method is much simpler and more efficient.
Thanks a ton!
Thanks for this wornderful article. Our entire team learnt new things.
-Naveen
Pinal very good article.
Thanks
Shyam
SELECT
STUFF(
(
SELECT
‘,’ + cast(Citation_Id as nvarchar(500))
FROM tollplus.violated_trips
FOR XML PATH(”)
), 1, 1, ”
) As CitationId
here we are getting the column values with CSV, bt i want to get with html tag then..
plz send me the solution
SELECT DISTINCT ISSUE_ID,
STUFF ((SELECT ‘, ‘ + PO_NUMBER + ‘ ( ‘+ CONVERT(VARCHAR(30), QUANTITY_ISSUED) + ‘ ‘ + UOM +’ ) ‘ FROM @temp WHERE CHLD.ISSUE_ID = MAIN.ISSUE_ID FOR XML PATH(”)), 1, 1, ”)AS PO_NUMBER
FROM @temp AS MAIN
Why r u use SubString function and give the 20000 fixed length.
If i have a very big query then what happend?????????????????
I have one table. One column ‘Name’ got values like – “1234,abcdef”
So, I want to display Name values like “abcdef”. Need to avoid “1234,” in value.
What function we have to use for this. Please let me know the sql query.
Thanks
select substring(col,patindex(‘%[a-b]%’,col),col),len(col)) from table
SELECT PARSENAME(REPLACE(name, ‘ ‘, ‘.’), 2) AS [FirstName],
PARSENAME(REPLACE(name, ‘ ,’, ‘.’), 1) AS [LastName]from table
SELECT PARSENAME(REPLACE(col, ‘ ,’, ‘.’), 1) AS [Name]from table