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
Hello,
I have to insert csv or excel file into sql server table.my table contains 6 fields but i want to insert only single column values from excel/csv.
please give me reply.
Thank you.
Refer this. It will solve your problem
Hi all,
I need to convert the values in a column which is separated by comma to different column as follows,
Address Id Phone
——————————————————————–
345 Ram Nagar,Mumbai,MH,425124,India 12345 9945721543
123 rajaji Nagar,Bangalore,KA,524212,India 67890 8235645678
convert to this
Address Street City State Zip Country Id Phone
————————————————————————————–
same as above 345 Ram Nagar Mumbai MH 425124 India 12345 9945721543
same as above 123 rajaji Nagar Bangalore KA 524212 India 67890 8235645678
Refer this method
Pinal, can you please help me with this. I know I can go with a stored proc, but for some reasons, I shouldn’t use it.
allowed_file_extensions in table UPLOAD has comma separated values from table FILE_EXTENSIONS
if allowed_file_extensions has atleast one entry with comma separated, and that has is_enabled = 0, then it should return 0.
It will return 1 only if for all values (comma separted) has is_enabled = 1
FILE_EXTENSIONS
id description is_enabled
1 .xls 1
2 .doc 1
3 .pdf 1
4 .rtf 1
5 .gif 1
6 .jpeg 1
7 .jpg 1
8 .docx 1
9 .mpeg 0
10 .mp3 0
UPLOAD
upload_id upload_name allowed_file_extensions
1 Template1 .xls, .doc, .pdf, .mp3, .mpeg, .pptx, .txt
2 Template2 .xls, .doc, .pdf, .rtf, .mp3, .jpeg, .jpg, .pptx, .txt
3 Template3 .xls, .doc, .pdf, .rtf, .gif, .pptx, .txt
Required Output in a view
upload_id is_enabled allowed_file_extensions
1 0 .xls, .doc, .pdf, .mp3, .mpeg, .pptx, .txt
2 0 .xls, .doc, .pdf, .mp3, .jpeg, .jpg, .pptx, .txt
3 1 .xls, .doc, .pdf, .rtf, .gif, .pptx, .txt
Thanks Pinal….the above xample which you have mentioned regarding that coma separated values(CSV)….thast amazing and helped a lot
Regards,
Yash
Hi Folks,
I have a integer field. For an instance i have a value like this 1500000. I want to return the value like this 1,500,000.
Any idea about this.
Thank you
Muthu
This is the formation issue that should be done in the front end application
Hi,
How can I insert comma separated strings in to diffrent rows of a table, with a single insert statement?
Use Bulk insert statement
Your information helped me a lot. thanks
Pinal Dave,
Thank you for all that you do.
I was wondering why you don’t like Greeks, Bulgarians, Russians, Chinese, Koreans and Thais?
Their language gets question marked when I run your T-SQL on the sys.syslanguages table in SQL 2008 R2.
SELECT langid, name,alias FROM sys.syslanguages
SELECT @value = SUBSTRING(
(SELECT ‘,’ + l.name
FROM sys.syslanguages l
FOR XML PATH(”)),2,200000)
us_english,Deutsch,Français,???,Dansk,Español,Italiano,Nederlands,Norsk,Português,Suomi,Svenska,ceština,magyar,polski,româna,hrvatski,slovencina,slovenski,e???????,?????????,???????,Türkçe,British,eesti,latviešu,lietuviu,Português (Brasil),????,???,????,Arabic,???
What is the datatype of @value? It should be nvarchar(max). Also you can simply use
SELECT SUBSTRING(
(SELECT ‘,’ + l.name
FROM sys.syslanguages l
FOR XML PATH(”)),2,200000) as csv
For anybody that had the issue of any ampersands being entitized as “& amp;” in your query result – see below for the syntax. That baked my head for a few hours. It uses the xquery .value to convert back to Nvarchar to avoid that issue. Example below Field1 is the key and FieldText is the one I want to summarize as a comma separated value list.
SELECT DISTINCT tm.Field1
STUFF(( SELECT DISTINCT ‘, ‘ + [FieldText] as [text()]
FROM Table1 ts
WHERE tm.Field1 = ts.Field1
FOR XML path(”), TYPE
).value(N’.’, N’nvarchar(max)’), 1, 1,”) as Field1CSV
FROM Table1 tm
I HAVE THE BELOW SCRIPT
select DISTINCT expiry from Rec_Dtl where grn_number =27964 and m_code =’M605′
THE RERSUTLS ARE
exiry
——-
382268; 07/02/05
976131, 31/05/2011
I WANT THE SEPRATE TWO VALUES LIKE BELOW.
DISTINCT EXPIRY 1 , EXPRIY 2
————————————-
382268 07/02/05
976131 31/05/2011
I WANT RESULTS EXACTLY LIKE ABOVE. PLEASE HELP ME.
Pinal Dave,
I have the following query:
SELECT
c.CompanyID,
c.CompanyName,
ISNULL(c.LogoFileName,”) AS LogoFileName,
ISNULL(c.City,”) AS City,
ISNULL(s.strState,”) as [State],
ISNULL(m.Mode,”) as Mode
FROM tblCompany c
INNER JOIN tblCompanyPartners tcmp on c.CompanyID = tcmp.PartnerCompanyID
LEFT OUTER JOIN tblStates s ON C.StateID = S.intStateID
LEFT OUTER JOIN tblCompanyModes cm on c.CompanyID = cm.CompanyID
LEFT OUTER JOIN tblMode m ON cm.ModeID = m.ModeID WHERE c.PartnerTypeID IN (1,2)
AND C.CompanyID 31
and c.CompanyID not in (select PartnerCompanyID from tblCompanyCarrierPartners where [Status] IN (‘Blocked’,’Requested’,’Accepted’) and tblCompanyCarrierPartners.CompanyID = 31) order by companyid desc
The following result is returned:
18 christ carrier company file1 San Jose New Hampshire LTL
18 christ carrier company file1 San Jose New Hampshire Oversize
14 Milton file2 Dallas California Intermodal
Now, I need to get the following result:
18 christ carrier company file1 San Jose New Hampshire LTL, Oversize
14 Milton file2 Dallas California Intermodal
I am really stuck up with this task. Any help is appreciated.
Thanks,
Varma
found something even more easy
SELECT STUFF((SELECT ‘,’ + Table.ColumnName FROM Table FOR XML PATH(”)),1, 1, ”) AS CSVColumn
My Table is Like below
Create table ABC (column nvarchar(2000))
insert ABC values (‘ab1|ab2|ab3|ab4|ab5|ab6|ab7|ab8’)
insert ABC values (‘bc1|bc2|bc3|bc4|bc5|bc6|bc7|bc8’)
here i want only display like below
ComnnXYZ
ab7
bc7
How to do a select statement for value stored in comma separated in db.
i have two table named parent and child parent table have PID which is primary key declared as foreign key for child table i want to display results like
PID ChildID
1 1,2,3
2 4,5
Is it possible for group_concat for two tables in sql 2008 ?
How to get reverse of it means if we have a variable having comma seprated vaules how to get them into a column so we can have operation on it
thanks.. helpful info
I’m a total SQL newbie, but I found I needed to dump some data from MS SQL to CSV and tried to hack it myself. The frustration with my code and this code is that it doesn’t create proper CSV (e.g. if you have a comma or n in one of your fields you’re screwed). I wrote a bunch of ugly code to add quotations around each column and replace all of the newlines with spaces. Could your solution incorporate something like that so we could get proper CSV out of MS SQL?
Hi,
Its good one to get d value in comma.
But my query is littel bit different:
What if I want comma sepereated value for more than two column?
Agrment Schedule ThirdParty Billing Copmany
A001000020 1 T001000010 1 ABC
A001000020 1 T001000017 1 PQR
A001000020 1 T001000034 1 XYZ
For the above details i wanna result like
Agreement Schedule Thirdparty Billing
A001000020 1 T001000010,T001000017, T001000034 1
Copmany
ABC,PQR,XYZ
Hi, If a column contains coma separated numbers, how to remove a particular number?
Say column “ID” contains 100,200,300,400,1000,2100,100
I would like to remove 100
If I use Replace function, it would modify 1000 and 2100 as well which I don’t want .
It should remove only 100 which may present at any index of the column ( 100, 200,100)
How to achieve this?
use this approach
replace(‘,’+col+’,’,’,100,’,’,’)