SQL Server – T-SQL – Different ways to generate a comma-separated string from a table


Recently, someone in the team faced a fairly common requirement – to generate a comma-separated string from values stored in a table. This being the last post of the year, I thought of sharing the 2 most-commonly used methods I know of implementing this requirement.

Do you know any other? If you share it on this post, I will publish it with due credit on my blog.

----------------------------------------------------
--WARNING: THIS SCRIPT IS PROVIDED AS-IS AND WITHOUT
--         WARRANTY.
--         FOR DEMONSTRATION PURPOSES ONLY       
----------------------------------------------------
--Step 01: Generate Temp table to store source data
DECLARE  @NamesTable TABLE (Id INT,
                            Name NVARCHAR(50))

--Step 02: Generate test data
INSERT INTO @NamesTable VALUES (1,'A'),
                               (2,'D'),
                               (2,'C'),
                               (3,'E'),
                               (3,'H'),
                               (3,'G')

--Option 01: My favourite
DECLARE @listStr VARCHAR(MAX) --DO NOT initialize this one!

SELECT @listStr = COALESCE(@listStr + ',' ,'') + nt.Name
FROM @NamesTable nt

SELECT @listStr

--Option 02: Using XML
; WITH CommaSeparatedXML (CommaSeparatedXML)
AS (SELECT CAST((SELECT (',' + nt.Name)
                 FROM @NamesTable nt
                 FOR XML PATH('')) AS NVARCHAR(MAX))
   )
SELECT SUBSTRING(CommaSeparatedXML, 2, LEN(CommaSeparatedXML))
FROM CommaSeparatedXML
GO

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

7 thoughts on “SQL Server – T-SQL – Different ways to generate a comma-separated string from a table

  1. Nakul Vachhrajani

    You may want to visit this extension post from @SQLZealot that deals with generating grouped comma-separated strings: [http://beyondrelational.com/blogs/sqlzealot/archive/2012/01/02/tsql-script-generating-concatenating-values-into-a-comma-separated-string-with-a-grouping.aspx][1]

    [1]: http://beyondrelational.com/blogs/sqlzealot/archive/2012/01/02/tsql-script-generating-concatenating-values-into-a-comma-separated-string-with-a-grouping.aspx

    Like

    Reply
  2. marc_jellinek@hotmail.com

    Another option is to use PIVOT. Not nearly as flexible as the FOR XML PATH(”) solution

    DECLARE @NamesTable TABLE (Id INT, Name NVARCHAR(50))

    INSERT INTO @NamesTable (Id, Name)
    VALUES 
        (1, 'A'), 
        (2, 'D'), 
        (2, 'C'), 
        (3, 'E'), 
        (3, 'H'), 
        (3, 'G')
    
    SELECT
        ISNULL(CAST([1] as varchar(max)) + ',', '') + 
        ISNULL(CAST([2] as varchar(max)) + ',', '') + 
        ISNULL(CAST([3] as varchar(max)) + ',', '') + 
        ISNULL(CAST([4] as varchar(max)) + ',', '') + 
        ISNULL(CAST([5] as varchar(max)) + ',', '') + 
        ISNULL(CAST([6] as varchar(max)), '') 
    FROM
        (SELECT ROW_NUMBER() OVER (ORDER BY Name) as RowNum, Name FROM @NamesTable) Base
        PIVOT
            (
                MIN(Name)
                FOR RowNum IN ([1],[2],[3],[4],[5], [6])
            ) pvt</code>
    

    Like

    Reply
  3. TheGeneral

    Using CTE you can avoid using variable declaration.

    ;WITH CTE(x, name )
    AS
    (SELECT ROW_NUMBER() OVER (ORDER BY Id ), NAME FROM @NamesTable )
    ,CSV(X,Result)
    AS
    (SELECT x, CONVERT(varchar(max),NAME) FROM CTE WHERE x = 1
    UNION ALL
    SELECT CSV.x+1, CONVERT(varchar(max),Result +',' + NAME )
    FROM CSV
    INNER JOIN CTE
    ON CSV.X+1 = CTE.x )
    SELECT Result FROM CSV T1
    WHERE NOT exists (SELECT 1 FROM CSV T2 WHERE T1.X < T2.X )

    Like

    Reply
  4. Aaron N. Cutshall

    Another possibility is to avoid both the variable AND the CTE:

    SELECT STUFF((SELECT (‘,’ + nt.Name)
    FROM @NamesTable nt
    FOR XML PATH(”)), 1, 1, ”);

    Like

    Reply
  5. Nakul Vachhrajani

    Great feedback, everyone! Thank-you very much!

    @marc_jellinek: PIVOT is possible, but here’s the issue. Conventional PIVOT would work great for a fixed number of strings. The option is to use dynamic PIVOT, which would be perhaps be the costliest of all operations.
    @Eric Bradford: Great idea on using the CTE. I will try it out and compare it with the others from a performance perspective.
    @Aaron N. Cutshall: Using XML is a good option, but I find it to be costly for this task. It does make for a compact, clean code, though.

    Keep the feedback coming, and have a nice day!

    Like

    Reply
  6. Aaron N. Cutshall

    Nakul: Your comment about XML is valid enough and the STUFF operation on top of that does take additional time. I agree that the COALESCE option is the most efficient but only when you have the luxury to use a variable. In most cases when I’ve had a similar need I needed the results to be from a query and the variable option was not possible.

    Like

    Reply
  7. Nakul Vachhrajani

    @Aaron N. Cutshall: Absolutely – if the luxury of using a variable is not available, then using XML operations is a good approach.

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

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