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
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.
i am que this table data one rows one colume Ahmedabad_Bhavnagar,Ahmedabad_Dahod,Ahmedabad_Gandhidham,Ahmedabad_Jamnagar
how to diff , comm
my data input–Ahmedabad_Bhavnagar,Ahmedabad_Dahod,Ahmedabad_Gandhidham,
outpunt–Ahmedabad_Bhavnagar
Ahmedabad_Dahod
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..
SELECT Value, DENSE_RANK() OVER (ORDER BY Value) AS ‘Dense Rank’ FROM Table
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 ?
Is there a way that i could import columns of a table from one DB to another DB?
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
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
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
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.
@Igal – I am glad that it helped you.
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:
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..
It is complete garbage that there is still no clean way to do this.
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.
Absolutely, it does and I am so looking forward to it.
Hi every one
Plz I need Query deep matching between tow databases also some statistics
Thnks
you can use SSDT and compare two databases.
Still it does not solve “Headers” issues. Mostly CSV files needs headers.
You can use Import Export Wizard.
is there way to do the same and remove duplicates while creating the csv?
use DISTINCT
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
How would you address column data as ‘Fruit=Apple,Color=Red,Source=Tree’. I want a columns for Fruit, Color, Source with their respective values, Apple, Red, Tree returned.
VERY NICE BUT…
try this on a numeric or integer field. int won’t convert to varchar.
Just cast it works pretty well, CAST(myId as varchar). Nice article BTW.