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.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
Could you explain (or point me to a good resource) “FOR XML PATH”?
I was actually working on a similar query today and wanted to order the select for my CSV query, but got an error indicating you can’t use an ORDER BY clause in a derived table and suggesting the use of FOR XML PATH.
Thanks!
Ryan
I have always wondered why they don’t put a Concatenate function into SQL Server.
Although the CTE approach might be the most “SQL” method of concatenation, I personally prefer the CLR approach. Here is a pretty good example of this:
h ttp://msdn.microsoft.com/en-us/library/ms254508%28VS.80%29.aspx
I also believe that this is a good article discussing the many many ways that people try to concatenate records:
h ttp://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/
The result is perfect but find a bit complicated…what is 2,200000 has to do in this????
is it the RANGE????
Thanks,
Nitin Sharma
DECLARE @a AS VARCHAR(4000)
SET @a =”
SELECT @a = @a + NAME + ‘,’
FROM HumanResources.Shift
SELECT @a
I’ve used a managed function with a StringBuilder – it results in a better performance.
@Ryan
Pinal has an article here on creating an XML file with FOR XML PATH.
Pinal also has an article on this topic, pointing to an external source: http://blog.sqlauthority.com/2008/08/17/sql-server-xml-split-a-delimited-string-generate-a-delimited-string/
Hi Nitin,
The 2,200000 is part of the SUBSTRING Function arguments, it says grab the characters starting from 2nd position to position 200000
O i c…. i guessed it right…
Thanks @ ramdas.
We can use stuff fn instead of substring, just to keep the code clean.
Also, it will be helpful if we want to handle data larger than 200000
select stuff(query using XML path),1,1,”) AS csv
The 2, 200000 is ugly as previously pointed out.
Code that imposes hard coded restrictions like this should not be used as suggested best practices or as teaching statements.
DECLARE @Csv varchar(Max)
SELECT @Csv= COALESCE(@Csv + ‘, ‘, ”) +
CAST(s.Name AS varchar(50))
FROM HumanResources.Shift s
ORDER BY s.Name
SELECT @Csv
I like your solution but I find that it in many ways is a one off solution and unfortionately will not work if you have multiple columns. Let say you had two tables
Table A
->a_id
Table B
->b_id
->name
->a_id
And you wanted the following results
[a_id],[name,name,name,name]
If you want to achieve multiple columns you need to create a User Defined Aggregate (see below) so that all you need to do is write a simple query like the one below
Select a_id, toCSV(name)
FROM A
INNER JOIN B on b.a_id = A.a_id
[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined, //use clr serialization to serialize the intermediate result
IsInvariantToNulls = true, //optimizer property
IsInvariantToDuplicates = false, //optimizer property
IsInvariantToOrder = false, //optimizer property
MaxByteSize = 8000) //maximum size in bytes of persisted value
]
public class toCSV : IBinarySerialize
{
///
/// The variable that holds the intermediate result of the concatenation
///
private StringBuilder intermediateResult;
///
/// Initialize the internal data structures
///
public void Init()
{
this.intermediateResult = new StringBuilder();
}
///
/// Accumulate the next value, not if the value is null
///
///
public void Accumulate(SqlString value)
{
if (value.IsNull)
{
return;
}
this.intermediateResult.Append(value.Value).Append(‘,’);
}
///
/// Merge the partially computed aggregate with this aggregate.
///
///
public void Merge(toCSV other)
{
this.intermediateResult.Append(other.intermediateResult);
}
///
/// Called at the end of aggregation, to return the results of the aggregation.
///
///
public SqlString Terminate()
{
string output = string.Empty;
//delete the trailing comma, if any
if (this.intermediateResult != null
&& this.intermediateResult.Length > 0)
{
output = this.intermediateResult.ToString(0, this.intermediateResult.Length – 1);
}
return new SqlString(output);
}
public void Read(BinaryReader r)
{
intermediateResult = new StringBuilder(r.ReadString());
}
public void Write(BinaryWriter w)
{
w.Write(this.intermediateResult.ToString());
}
}
Hi Bret,
I was looking for the exact solution. Your toCSV function helped me achieve a solution that I was looking for.
Thanks,
Jitendra
hi
I have a table ‘company’ and it have a column ‘keywords’ where the values are stored like
KEYWORDS
Row1 ac, bg, hkl, jh, od
Row2 gh,jd,kl,ks
Now what i need is that i want to create a new table name as keywords and it store data like
KEYWORDS
Row1 ac
Row2 bg
Row3 hkl
Row4 jh
…………………………
……………………..
IS it possible to do that??
please help
Thanks and regards.
Pinal has a UDF that will do it: http://blog.sqlauthority.com/2007/05/06/sql-server-udf-function-to-convert-list-to-table/
Brad Schulz has a detailed article o doing this with FOR XML: http://bradsruminations.blogspot.com/2009/10/un-making-list-or-shredding-of-evidence.html
Hi,
The same thing can be performed using below function also:
CREATE FUNCTION [dbo].[Split](@List VARCHAR(6000), @SplitOn VARCHAR(5))
RETURNS @RtnValue TABLE([Value] VARCHAR(100))
AS
BEGIN
WITH s(start) AS
(
SELECT DISTINCT CHARINDEX(‘,’,',’+@List+’,',p)
FROM
(SELECT number p FROM master..spt_values WHERE type=’p’ and numbers.start)-start-1)
FROM s WHERE start<len(@List)+2
)
INSERT INTO @RtnValue (Value)
SELECT chunk FROM chunks
RETURN
END
—Use the function Split with your table as below:
SELECT fSplit.Value AS Result
FROM CROSS APPLY Split(,’,') fSplit
Kind Regards,
Pinal Dave
[...] The article which was featured in the show is SQL SERVER – Comma Separated Values (CSV) from Table Column. [...]
The script you’ve previously described at http://blog.sqlauthority.com/2007/05/06/sql-server-udf-function-to-convert-list-to-table/
works very well so my problem is fixed.
To quibble, the other script you’ve given in-thread here : function split – fails on my SQL Server 2005.
It complains about – Incorrect syntax near ‘‘’ on line 7 and 9.
Still, the other one works fine so STILL thanks.
-skk
[...] by pinaldave In my earlier post, I wrote about how one can use XML to convert table to string SQL SERVER – Comma Separated Values (CSV) from Table Column. The same article is also published on channel 9 SQLAuthority News – Featured on Channel 9. One [...]
Hi,
I have a table like,
ID | Value
—- ——–
1000 1
1000 2
1000 3
1001 2
1001 3
1001 4
1001 5
I want the output like this
1000 | 1,2,3
1001 | 2.3.4.5
But when i use the below query,
SELECT ID,(STUFF(select ‘, ‘+value from tblA group by value FOR XML PATH(”)),1,2,”) FROM tblA
I am getting the output like shown below
1000 | 1,2,3,4,5
1001 | 1,2,3,4,5
Which is wrong.. please help
Regards
Hi Taher,
I found that you applied GROUP BY at wrong place.
To achieve result as expected you need to change query to:
DECLARE @tblA TABLE(ID INT, Value INT)
INSERT INTO @tblA VALUES(1000,1)
INSERT INTO @tblA VALUES(1000,2)
INSERT INTO @tblA VALUES(1000,3)
INSERT INTO @tblA VALUES(1001,2)
INSERT INTO @tblA VALUES(1001,3)
INSERT INTO @tblA VALUES(1001,4)
INSERT INTO @tblA VALUES(1001,5)
–SELECT * from @tblA
SELECT ID,
SUBSTRING(
(
select ‘ ,’+ CAST(value AS VARCHAR)
from @tblA b
WHERE a.ID = b.ID
FOR XML PATH(”)
)
,3,100)
FROM @tblA a
GROUP BY a.ID
Let me know if it helps you.
Thanks,
Tejas
SQLYoga.com
Hello Tejas,
Thank you very much for your reply. It solved my problem. I was doing group by inside because i wanted to club all similar records (main intention being not to get duplicate records). But now i figured that same thing can be achieved by using DISTINCT.
Thanks A lot !!!
Taher
consider i am having a employee table . iwant my result to be dispalyed like the following.
emp name reporting order
steve \steve
austin \steve\austin
andrew \steve\austin\andrew
bruce \steve\austin\andrew\bruce
thank you in advance.
Akhiesh
I got a little giddy when I tried this query on one of the databases I work on. I had used SQL functions to do this in the past… great post! Thanks for sharing.
How would you remove duplicate comma seperated value from below query (sai,sam,sai)
USE tempdb;
GO
–drop table t1
CREATE TABLE t1 (id INT, NAME VARCHAR(MAX));
INSERT t1 values (1,’Jamie’);
INSERT t1 values (1,’Joe’);
INSERT t1 values (1,’John’);
INSERT t1 values (2,’Sai’);
INSERT t1 values (2,’Sam’);
INSERT t1 values (2,’Sai’);
GO
select
id,
stuff((
select ‘,’ + t.[name]
from t1 t
where t.id = t1.id
order by t.[name]
for xml path(”)
),1,1,”) as name_csv
from t1
group by id
;