SQL SERVER – Comma Separated Values (CSV) from Table Column

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.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQL SERVER – Interesting Observation – TOP 100 PERCENT and ORDER BY
Next Post
SQLAuthority News – SQL Server 2008 Analysis Services Performance Guide

Related Posts

126 Comments. Leave new

  • Why r u use SubString function and give the 20000 fixed length.
    If i have a very big query then what happend?????????????????

    Reply
  • 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

    Reply
    • select substring(col,patindex(‘%[a-b]%’,col),col),len(col)) from table

      Reply
    • mahalakshmi
      May 9, 2013 1:03 pm

      SELECT PARSENAME(REPLACE(name, ‘ ‘, ‘.’), 2) AS [FirstName],
      PARSENAME(REPLACE(name, ‘ ,’, ‘.’), 1) AS [LastName]from table

      Reply
    • mahalakshmi
      May 9, 2013 1:06 pm

      SELECT PARSENAME(REPLACE(col, ‘ ,’, ‘.’), 1) AS [Name]from table

      Reply
  • 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.

    Reply
  • 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

    Reply
  • 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

    Reply
  • Thanks Pinal….the above xample which you have mentioned regarding that coma separated values(CSV)….thast amazing and helped a lot

    Regards,
    Yash

    Reply
  • 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

    Reply
  • Hi,

    How can I insert comma separated strings in to diffrent rows of a table, with a single insert statement?

    Reply
  • Your information helped me a lot. thanks

    Reply
  • 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,???

    Reply
    • 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

      Reply
  • 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

    Reply
  • 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.

    Reply
  • Santosh Varma
    January 30, 2012 3:07 pm

    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

    Reply
  • found something even more easy

    SELECT STUFF((SELECT ‘,’ + Table.ColumnName FROM Table FOR XML PATH(”)),1, 1, ”) AS CSVColumn

    Reply
  • 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

    Reply
  • How to do a select statement for value stored in comma separated in db.

    Reply
  • 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 ?

    Reply
  • Sahil kakkar
    April 12, 2012 2:16 am

    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

    Reply
  • thanks.. helpful info

    Reply
  • 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?

    Reply

Leave a Reply Cancel reply

Exit mobile version