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

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)

About these ads

14 thoughts on “SQL SERVER – Comma Separated Values (CSV) from Table Column – Part 2

  1. 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?

    Like

  2. 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?

    Like

  3. 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.

    Like

  4. 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

    Like

  5. 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?

    Like

  6. Pingback: SQL SERVER – Importing CSV File Into Database – SQL in Sixty Seconds #018 – Video « SQL Server Journey with SQL Authority

  7. Pingback: SQL SERVER – Grouping by Multiple Columns to Single Column as A String « SQL Server Journey with SQL Authority

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #008 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s