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 of the very interesting points that was discussed on show was about the usage of function SUBSTRING. I found the following point very valid: SUBSTRING usage limits the length of the XML to be used.
I have re-written the same function with function STUFF, and it removes any limit imposed on the script.
USE AdventureWorks
GO
-- Check Table Column
SELECT [Name]
FROM HumanResources.Shift
GO
-- Get CSV values
SELECT STUFF(
(SELECT ',' + s.Name
FROM HumanResources.Shift s
ORDER BY s.Name
FOR XML PATH('')),1,1,'') AS CSV
GO
Do let me know your thoughts on the same.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
This function is an attempt to reproduce the functionality of the build-in group_concat from MySQL, why is it that this is still missing from SQL Server?
Yes. MySQL has a Group_concat function to do the same task
But usually this kind of thing should be done in front end application
Hi Pinal,
Please check this way, and let me know is it okay or it has some issues?
GO
– We can write a function for it
CREATE FUNCTION [dbo].[GetShifts] ()
RETURNS NVARCHAR(MAX)
WITH EXECUTE AS CALLER
AS
BEGIN
declare @output varchar(max)
set @output = null;
select @output = COALESCE(@output + ‘, ‘, ”) + convert(varchar(max),[Name])
from [MyRnDDataBase].[dbo].[HumanResources.Shift]
RETURN @output
END
– Check Table Column
SELECT [Name]
FROM [MyRnDDataBase].[dbo].[HumanResources.Shift]
– AND Now we’ll just call that function
SELECT [dbo].GetShifts() AS CSV
It’ll give you the same result plus it provides code reusability as we just need to call the function wherever necessary. Isn’t it?
Pinal, you still have to look out for some special characters when performing a concatenation with FOR XML PATH clause. For example, if you would like to concatenate three values: aaa, bbb and you would get < and > instead of chars. I would rather recommend writing your own CLR aggregate function for this kind of operation.
Huh, special chars were removed from my previous comment :-)
Dave, you’re the man!
Is there a way to remove duplicates?
for e.g. Table has two columns
Col A Col B
Test1 Val1
Test2 Val2
Test1 Val3
if i have select stuff on colA and stuff on colB the result is something like
Col A ColB
Test1,Test2, Val1,Val2,Val3
Test1
how would i remove duplicate from ColA and display only Test1,Test2
Hi Manish,
You can generate list of comma separated file as described here at: http://www.sqlyoga.com/2009/02/sql-server-get-comma-separated-list.html
Thanks,
Tejas
SQLYoga.com
@manish
you can use distinct before column name
I agree, we cannot use this if the strings contain special characters like ampersand,less than , etc
Is there another solution apart from using COLAESECE?
[...] SQL SERVER – Comma Separated Values (CSV) from Table Column – Part 2 [...]
[...] Comma Separated Values (CSV) from Table Column – Part 2 [...]
[...] Comma Separated Values (CSV) from Table Column It is a very common request to create List or CSV from Table Column. We are going to explore the same concept in this blog – I have tried to use XML to generate the solution of this issue. Here is the blog post I wrote for the same subject earlier: Comma Separated Values (CSV) from Table Column [...]