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))
   )

Read more: Beyond Relational
QR:

Posted via email from Jasper-net