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.
SQLTwins by 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
LikeLike
Beyondrelational site is no longer available, the content is available in the below link:
LikeLiked by 1 person
Another option is to use PIVOT. Not nearly as flexible as the FOR XML PATH(”) solution
DECLARE @NamesTable TABLE (Id INT, Name NVARCHAR(50))LikeLike
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 )
LikeLike
Another possibility is to avoid both the variable AND the CTE:
SELECT STUFF((SELECT (‘,’ + nt.Name)
FROM @NamesTable nt
FOR XML PATH(”)), 1, 1, ”);
LikeLike
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!
LikeLike
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.
LikeLike
@Aaron N. Cutshall: Absolutely – if the luxury of using a variable is not available, then using XML operations is a good approach.
LikeLike
Pingback: #0415 – SQL Server – Performance Tuning – Use STRING_AGG to generate comma separated strings | SQLTwins by Nakul Vachhrajani