CODEWITHSUNDEEP
sql-servert-sql
Ritchie B
Ritchie B

Reputation: 85

Concatenate rows into single column

I have seen the below as a solution to concatenating the values of a number of rows into a single string, however I cannot understand how it actually works.

DECLARE @ConcatString nvarchar(max);

SELECT @ConcatString = COALESCE(@ConcatString + ', ['+ cast([Dice1] as 
nvarchar(10)) + ']', '['+ cast([Dice1] as nvarchar(10)) + ']')
FROM  [dbo].[Dice]

SELECT @ConcatString

Output: [1], [2], [3], [4], [5], [6]

Please could you explain how it works, and if there are any risks using this approach.

Thanks in advance

Upvotes: 1

Views: 88

Answers (1)

Alan Burstein
Alan Burstein

Reputation: 7918

One Final Edit: Updated my solution to include parenthesis.

That approach works by appending the values from dbo.dice to your variable, @concatString. It's not reliable; see this article. You can use FOR XML PATH or STRING_AGG on 2017+. Both methods are nasty fast. When using FOR XML PATH you must include an ORDER BY if the order of items must be guaranteed. STRING_AGG is my favorite method by far. 

-- Sample data
DECLARE @dice TABLE (dice1 TINYINT);
INSERT @dice(dice1)
VALUES(1),(2),(3),(4),(5),(6);

-- Solution #1: FOR XML PATH
SELECT newString = STUFF((
SELECT   ',('+CAST(d.dice1 AS VARCHAR(8000))+')'
FROM     @dice AS d
ORDER BY d.dice1
FOR XML PATH('')),1,1,'');

-- Solution #2: STRING_AGG (SQL 2017+)
SELECT newString = STRING_AGG(CONCAT('(',d.dice1,')'),',') WITHIN GROUP (ORDER BY d.dice1)
FROM     @dice AS d;

Upvotes: 2

Related Questions