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.
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]
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))
Using CTE you can avoid using variable declaration.
;WITH CTE(x, name )
(SELECT ROW_NUMBER() OVER (ORDER BY Id ), NAME FROM @NamesTable )
(SELECT x, CONVERT(varchar(max),NAME) FROM CTE WHERE x = 1
SELECT CSV.x+1, CONVERT(varchar(max),Result +',' + NAME )
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 )
Another possibility is to avoid both the variable AND the CTE:
SELECT STUFF((SELECT (‘,’ + nt.Name)
FROM @NamesTable nt
FOR XML PATH(”)), 1, 1, ”);
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!
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.
@Aaron N. Cutshall: Absolutely – if the luxury of using a variable is not available, then using XML operations is a good approach.
Pingback: #0415 – SQL Server – Performance Tuning – Use STRING_AGG to generate comma separated strings | SQLTwins by Nakul Vachhrajani