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.

SQL SERVER - Comma Separated Values (CSV) from Table Column csvxml

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

  • Touhid e Azhar
    October 5, 2012 5:29 pm

    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

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

    Reply
  • In a table with EmployeeName column, I am having employee names in lastname, firstname format & in some rows there are more that 1 employee name seperated by semicolon. How do I convert that column into firstname lastname format.

    Reply
  • i am que this table data one rows one colume Ahmedabad_Bhavnagar,Ahmedabad_Dahod,Ahmedabad_Gandhidham,Ahmedabad_Jamnagar
    how to diff , comm

    Reply
  • my data input–Ahmedabad_Bhavnagar,Ahmedabad_Dahod,Ahmedabad_Gandhidham,
    outpunt–Ahmedabad_Bhavnagar
    Ahmedabad_Dahod

    Reply
  • I am having value like this

    ID | Value
    —- ——–
    1000 1
    1000 2
    1000 3
    1001 2
    1001 3
    1001 4
    1001 5

    I want the output like this

    1000 | 1
    1000 | 1
    1000 | 1
    1001 | 2
    1001 | 2
    1001 | 2
    1001 | 2
    Please Help..

    Reply
  • Give me stored procedure using sql server,

    Table1:

    Id FullName
    ——————-
    1 Cleo,Smith,james,yanee

    Table2:

    I want to separate the comma delimited string into 3 columns

    Id FullName Name Surname Last DES
    — ———— ——- ———– —— ——
    1 Cleo,Smith Cleo Smith james Yanee

    First table values separate comma vles after that values insert into Table2 ?

    Reply
  • Is there a way that i could import columns of a table from one DB to another DB?

    Reply
  • I have a CSV in a col in a table that I needed to flip, I used the following recursive CTE:
    WITH recCTE AS(
    SELECT
    startRead = CONVERT(BIGINT, 1),
    stopRead = CHARINDEX(‘;’, RTRIM(CSVColumn ) + ‘;’ ),
    CSVColumn ,
    ID
    FROM MyTableWithCSVCol
    WHERE CSVColumn IS NOT NULL
    UNION ALL
    SELECT
    startRead = stopRead + 1,
    stopRead = CHARINDEX(‘;’, RTRIM(CSVColumn) + ‘;’, stopRead + 1),
    CSVColumn,
    ID
    FROM recCTE
    WHERE stopRead > 0
    )

    SELECT ID, LTRIM(SUBSTRING(RTRIM(CSVColumn), startRead, stopRead – startRead)) CSVColumnFlipped
    FROM recCTE
    WHERE stopRead > 0 AND startRead stopRead
    ORDER BY ID, startRead

    Reply
  • Hi Sir, Help me on this..
    I want to generate CSV file with sum columns in a table. A column values have some comma separated values. eg: student_marks 76,65,86 like this… so plz help me how to do this

    Reply
  • i have two table one stock in columns name ItemId and Serial No,
    itemid is 10 and 9 and 11 or collumns name Serial No is AAH104657,AAH104658 and AAHGRT2345 and 11111
    collumns name qty ,qty is 2 and 1 and 1

    and second table stckout columns name ItemId and Serial No
    itemid is 10 and 9 and or collumns name Serial No is AAH104657,AAH104658 and AAHGRT2345 and
    collumns name qty ,qty is 2 and 1
    i need a query toal split serial no in stockin which is not in stockout and total oty in stock out and stockout.Please help me

    Reply
  • This is Awesome! For years I’ve been concatenating strings and worrying about the length limits of varchar and nvarchar types.

    This looks like a much better option and without those silly 8000 characters limits.

    Reply
  • Hi Pinal,
    Found something that seemed simpler to me.

    create table #user (username varchar(25))

    insert into #user (username) values (‘Paul’)
    insert into #user (username) values (‘John’)
    insert into #user (username) values (‘Mary’)

    declare @tmp varchar(250)
    SET @tmp = ”
    select @tmp = @tmp + username + ‘, ‘ from #user

    select SUBSTRING(@tmp, 0, LEN(@tmp))

    From:

    Reply
  • Hi Pinal,

    I have got below error while executing below query :

    Error : FOR XML could not serialize the data for node ‘NoName’ because it contains a character (0x0001) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.

    Query : SELECT STUFF((SELECT ‘;’ + CAST(x.vTest AS VARCHAR(250)) FROM View_Test AS x
    WHERE x.nPrimaryNo= t.nPrimaryNoORDER BY nTestNo FOR XML PATH (”),TYPE).value(‘./text()[1]’,’NVARCHAR(MAX)’),1,1,”)

    FROM View_Test t

    Please help me..

    Thank you very much..

    Reply
  • It is complete garbage that there is still no clean way to do this.

    Reply
  • The next version of SQL Server introduces STRING_AGG ( https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017 ), which is a very clean way to produce a CSV via an aggregate.

    Reply
  • Hi every one
    Plz I need Query deep matching between tow databases also some statistics
    Thnks

    Reply
  • Still it does not solve “Headers” issues. Mostly CSV files needs headers.

    Reply
  • is there way to do the same and remove duplicates while creating the csv?

    Reply
  • Sachin in need to generate different names for this six letter char from a table

    Column1. Column2

    A. X

    B. T

    C. U

    A. Y

    B. U

    First time replace the first a next time replace secon A

    Reply

Leave a Reply

Menu